Row to Column Data distribution

[arahim01]

Hi All,

I have tried to get data by using an excel formula. But I felt. Actually I want to generate data distribution from down row to column wise and right side.

Suppose, I have some data in row 1, 2, 3 and 4 by an household ID of that household's all 4 members. Now i want to take all members data to the same row and right side.

Please see the attached file. And help me please.

 

[Special-K99]

You cant attach files on this forum. There are tools on this forum for adding small spreadsheet images

https://www.mrexcel.com/forum/about-board/508133-attachments.html

Or upload the file to an online storage site and post a link to it, though some people may not download the file for fear of viruses.

 

[arahim01]

Thanks for your guidance. Could you please help me in this regards. The link is: https://drive.google.com/file/d/15jbgZXF60KyEt8VqeIE7ucWekEORbUh7/view?usp=sharing

Thanks in advance.

 

[Special-K99]

This is best achieved with VBA, are you happy with a VBA solution or must it be a formula?
I can't help if you want a formula solution.

 

[Special-K99]

This looks pretty good.
You'll have to format date cells and place headers at the top of the output sheet

Sub TableToColumns()
Application.ScreenUpdating = False
lastrow = Worksheets("RAW").Cells(Rows.Count, "A").End(xlUp).Row
Raw_Date = 0
Raw_Cluster = 0
Raw_Team = 0
Raw_Household = 0
j = 1
For i = 2 To lastrow
If Worksheets("RAW").Cells(i, 1) <> Raw_Date Or _
Worksheets("RAW").Cells(i, 2) <> Raw_Cluster Or _
Worksheets("RAW").Cells(i, 3) <> Raw_Team Or _
Worksheets("RAW").Cells(i, 4) <> Raw_Household Then
j = j + 1
k = 5
Worksheets("Dataset Template").Cells(j, 1) = Worksheets("RAW").Cells(i, 1)
Worksheets("Dataset Template").Cells(j, 2) = Worksheets("RAW").Cells(i, 2)
Worksheets("Dataset Template").Cells(j, 3) = Worksheets("RAW").Cells(i, 3)
Worksheets("Dataset Template").Cells(j, 4) = Worksheets("RAW").Cells(i, 4)
Raw_Date = Worksheets("RAW").Cells(i, 1)
Raw_Cluster = Worksheets("RAW").Cells(i, 2)
Raw_Team = Worksheets("RAW").Cells(i, 3)
Raw_Household = Worksheets("RAW").Cells(i, 4)
End If
For l = 0 To 7
Worksheets("Dataset Template").Cells(j, k + l) = Worksheets("RAW").Cells(i, 5 + l)
Next l
k = k + 8
Next i
End Sub