Re-alignment of data

shreeram.maroo
2StarLounger
Posts: 181
Joined: 19 Feb 2016, 16:54
Location: Veraval, India

Re-alignment of data

Post by shreeram.maroo »

Hi,

I have attached a sample file. I have a situation wherein the data is available with me in the shape and format as available in "Base file" tab. I want to re-align it and bring it in a shape and format as presented in "Result tab". Can anyone think of any trick or shortcut or macro. There are 600 such line items - sample file contains only two and manually doing it will consume almost a day.

Thanks in advance.
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78027
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Re-alignment of data

Post by HansV »

What do you mean by "sample file contains only two"?
Best wishes,
Hans

User avatar
SpeakEasy
4StarLounger
Posts: 510
Joined: 27 Jun 2021, 10:46

Re: Re-alignment of data

Post by SpeakEasy »

Your 'Base file' sheet seems to be inconsistent

snb
4StarLounger
Posts: 541
Joined: 14 Nov 2012, 16:06

Re: Re-alignment of data

Post by snb »

Code: Select all

Sub M_snb()
   sn = Sheet2.Cells(1).CurrentRegion
   ReDim sp(UBound(sn) - 1, UBound(sn, 2) - 1)
   
   For j = 1 To UBound(sn) - 1 Step 2
     sp(n, 0) = sn(1, 2)
     sp(n, 1) = sn(j + 1, 2)
     sp(n, 2) = sn(j + 2, 2)
     sp(n + UBound(sn) \ 2, 0) = sn(1, 3)
     sp(n + UBound(sn) \ 2, 1) = sn(j + 1, 3)
     sp(n + UBound(sn) \ 2, 2) = sn(j + 2, 3)
     
     n = n + 1
  Next
   
  Sheet1.Cells(2, 6).Resize(UBound(sp) + 1, UBound(sp, 2) + 1) = sp
End Sub

shreeram.maroo
2StarLounger
Posts: 181
Joined: 19 Feb 2016, 16:54
Location: Veraval, India

Re: Re-alignment of data

Post by shreeram.maroo »

HansV wrote:
30 Nov 2023, 14:15
What do you mean by "sample file contains only two"?
I mean the sample file data of contains only 2 unique ids. I have to work on around 600 such IDs

shreeram.maroo
2StarLounger
Posts: 181
Joined: 19 Feb 2016, 16:54
Location: Veraval, India

Re: Re-alignment of data

Post by shreeram.maroo »

SpeakEasy wrote:
30 Nov 2023, 15:02
Your 'Base file' sheet seems to be inconsistent
As in, can you elaborate ?

User avatar
rory
5StarLounger
Posts: 813
Joined: 24 Jan 2010, 15:56

Re: Re-alignment of data

Post by rory »

For example row 4 says UniqueID in column A but actually contains dates.
Regards,
Rory

shreeram.maroo
2StarLounger
Posts: 181
Joined: 19 Feb 2016, 16:54
Location: Veraval, India

Re: Re-alignment of data

Post by shreeram.maroo »

Oh i see, that's an input error. Unique ID comes only once and dates and units are repeated. Check now, have fixed that.
You do not have the required permissions to view the files attached to this post.

User avatar
rory
5StarLounger
Posts: 813
Joined: 24 Jan 2010, 15:56

Re: Re-alignment of data

Post by rory »

Here's a power query method.
You do not have the required permissions to view the files attached to this post.
Regards,
Rory

snb
4StarLounger
Posts: 541
Joined: 14 Nov 2012, 16:06

Re: Re-alignment of data

Post by snb »


shreeram.maroo
2StarLounger
Posts: 181
Joined: 19 Feb 2016, 16:54
Location: Veraval, India

Re: Re-alignment of data

Post by shreeram.maroo »

snb wrote:
01 Dec 2023, 08:55
Why ignoring https://eileenslounge.com/viewtopic.php ... 87#p312487 ?
Hi snb,
Tried it, it worked on my sample file. However not working on the actual file with 600 records ! Not able to understand why.

shreeram.maroo
2StarLounger
Posts: 181
Joined: 19 Feb 2016, 16:54
Location: Veraval, India

Re: Re-alignment of data

Post by shreeram.maroo »

rory wrote:
01 Dec 2023, 08:18
Here's a power query method.
Hi Rory, how to actually configure the power query to get such output ?

User avatar
rory
5StarLounger
Posts: 813
Joined: 24 Jan 2010, 15:56

Re: Re-alignment of data

Post by rory »

It's all in the sample workbook. You can just replace the table with a table of your actual data.
Regards,
Rory

shreeram.maroo
2StarLounger
Posts: 181
Joined: 19 Feb 2016, 16:54
Location: Veraval, India

Re: Re-alignment of data

Post by shreeram.maroo »

Allright, thanks a lot. This is really helpful and time saving

User avatar
DocAElstein
4StarLounger
Posts: 508
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

Re: Re-alignment of data

Post by DocAElstein »

Hello
Here’s another alternative VBA way, using the non looping pretty one line arrOut()=Index(arrIn(), Rws(), Clms()) way

This should do it for the supplied test data

Code: Select all

 Sub makroo()  '     https://eileenslounge.com/viewtopic.php?f=27&t=40371     https://www.excelfox.com/forum/showthread.php/2837-Appendix-Thread-App-Index-Rws()-Clms()-Majic-code-line-Codings-for-other-Threads-Tables-etc-)/page53#post19567   https://www.excelfox.com/forum/showthread.php/2837-Appendix-Thread-App-Index-Rws()-Clms()-Majic-code-line-Codings-for-other-Threads-Tables-etc-)/page53#post19568
' Unique ID
 Let Worksheets.Item(1).Range("A2:A27") = Application.Index(Worksheets.Item(2).Range("B1:C1"), 1, Evaluate("IF({1},INT(((Row(1:26)-1)/13))+1)"))
' Date & Units
 Let Worksheets.Item(1).Range("B2:C27") = Application.Index(Worksheets.Item(2).Range("B2:C27"), Evaluate("(((Row(1:26)-1)*2)+COLUMN(A:B))-((IF({1},INT((ROW(1:26)-1)/13)+1))-1)*26"), Evaluate("IF({1},INT((ROW(1:26)-1)/13)+1)"))
End Sub
Some explanations for how I did that
here
and
here

It is possibly easy to adapt that to the full data or any data if the typical format is known and regular. If you want help with that then let me see the full data so that I am sure what the typical base worksheet might look like.

_.__________

To test, run that macro on your returned file attached to this post. (There are a couple of extra worksheets to help with the explanations)



Alan
You do not have the required permissions to view the files attached to this post.
They'll burn down the synagogues at six o'clock, And we'll all go along like before
Del Amitri, 1989 , Alan 2024

shreeram.maroo
2StarLounger
Posts: 181
Joined: 19 Feb 2016, 16:54
Location: Veraval, India

Re: Re-alignment of data

Post by shreeram.maroo »

Thanks DocAElstein !