Re-alignment of data

[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.

[HansV]

What do you mean by "sample file contains only two"?

[SpeakEasy]

Your 'Base file' sheet seems to be inconsistent

[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]

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]

Your 'Base file' sheet seems to be inconsistent

As in, can you elaborate ?

[rory]

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

[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.

[rory]

Here's a power query method.

[snb]

Why ignoring https://eileenslounge.com/viewtopic.php ... 87#p312487 ?

[shreeram.maroo]

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]

Here's a power query method.

Hi Rory, how to actually configure the power query to get such output ?

[rory]

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

[shreeram.maroo]

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

[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

[shreeram.maroo]

Thanks DocAElstein !