Converting Multiple Column Data into a single table

[KolGuyXcel]

Hello,

I'm working on an excel sheet that I've received which has around 30 columns of data. Basically each column refers to one "table" and each "table" has exactly the same 6 columns. Now, each column has a variable 6N number of rows!
I need to compile all the data in a single 6 column table. So, 6 consecutive rows from each column in the "original" excel sheet refers to the same data and needs to go in the same row in the final 6-column table, which I intend to create in a separate sheet.




Note: A non-VBA solution is needed.

 

[maabadi]

First at sheet1, I add Manually Zero Values at Cell AI2 Then I add one Helper Column to Count Data at Each column. I add it at column AI (after 30 Columns).

Book1
	A	B	C	D	E	F	G	H
1	ItemCode1	ItemCode4	ItemCode6	ItemCode7	ItemCode8	ItemCode9	ItemCode10	ItemCode11
2	ItemName1	ItemName4	ItemName6	ItemName7	ItemName8	ItemName9	ItemName10	ItemName11
3	ItemRate1	ItemRate4	ItemRate6	ItemRate7	ItemRate8	ItemRate9	ItemRate10	ItemRate11
4	ItemQuantity1	ItemQuantity4	ItemQuantity6	ItemQuantity7	ItemQuantity8	ItemQuantity9	ItemQuantity10	ItemQuantity11
5	ItemPrice1	ItemPrice4	ItemPrice6	ItemPrice7	ItemPrice8	ItemPrice9	ItemPrice10	ItemPrice11
6	ItemExpiry1	ItemExpiry4	ItemExpiry6	ItemExpiry7	ItemExpiry8	ItemExpiry9	ItemExpiry10	ItemExpiry11
7	ItemCode2	ItemCode5	ItemCode7	ItemCode8	ItemCode9	ItemCode10	ItemCode11	ItemCode12
8	ItemName2	ItemName5	ItemName7	ItemName8	ItemName9	ItemName10	ItemName11	ItemName12
9	ItemRate2	ItemRate5	ItemRate7	ItemRate8	ItemRate9	ItemRate10	ItemRate11	ItemRate12
10	ItemQuantity2	ItemQuantity5	ItemQuantity7	ItemQuantity8	ItemQuantity9	ItemQuantity10	ItemQuantity11	ItemQuantity12
11	ItemPrice2	ItemPrice5	ItemPrice7	ItemPrice8	ItemPrice9	ItemPrice10	ItemPrice11	ItemPrice12
12	ItemExpiry2	ItemExpiry5	ItemExpiry7	ItemExpiry8	ItemExpiry9	ItemExpiry10	ItemExpiry11	ItemExpiry12
13	ItemCode3
14	ItemName3
15	ItemRate3
16	ItemQuantity3
17	ItemPrice3
18	ItemExpiry3
19
20
21
22
23
24
25
26
27
28
29
30
31
32
Sheet1

Book1
	AI
1	CountItemsatColumns
2	0
3	18
4	30
5	42
6	54
7	66
8	78
9	90
10	102
11	102
12	102
13	102
14	102
15	102
16	102
17	102
18	102
19	102
20	102
21	102
22	102
23	102
24	102
25	102
26	102
27	102
28	102
29	102
30	102
31	102
32	102
Sheet1

Cell Formulas
Range	Formula
AI3:AI32	AI3	=IFERROR(COUNTIFS(INDIRECT(ADDRESS(1,ROWS($A$1:$A$1))&":"&ADDRESS(1000,ROWS($A$1:A1))),"<>"),0)

Then At Sheet2 Try this formula:

Book1
	A	B	C	D	E	F	G
1	Codes	Names	Rate	Quantity	Price	Expiry
2	ItemCode1	ItemName1	ItemRate1	ItemQuantity1	ItemPrice1	ItemExpiry1
3	ItemCode2	ItemName2	ItemRate2	ItemQuantity2	ItemPrice2	ItemExpiry2
4	ItemCode3	ItemName3	ItemRate3	ItemQuantity3	ItemPrice3	ItemExpiry3
5	ItemCode4	ItemName4	ItemRate4	ItemQuantity4	ItemPrice4	ItemExpiry4
6	ItemCode5	ItemName5	ItemRate5	ItemQuantity5	ItemPrice5	ItemExpiry5
7	ItemCode6	ItemName6	ItemRate6	ItemQuantity6	ItemPrice6	ItemExpiry6
8	ItemCode7	ItemName7	ItemRate7	ItemQuantity7	ItemPrice7	ItemExpiry7
9
10
Sheet2

Cell Formulas
Range	Formula
A2:F8	A2	=OFFSET(Sheet1!$A$1,(ROW($A1)-1)*6+COLUMNS($A:A)-1-INDEX(Sheet1!$AI$2:$AI$31,MATCH((ROW($A1)-1)*6+COLUMNS($A:A)-1,Sheet1!$AI$2:$AI$31,1)),MATCH((ROW($A1)-1)*6+COLUMNS($A:A)-1,Sheet1!$AI$2:$AI$31,1)-1)

 

[KolGuyXcel]

First at sheet1, I add Manually Zero Values at Cell AI2 Then I add one Helper Column to Count Data at Each column. I add it at column AI (after 30 Columns).

Book1
	A	B	C	D	E	F	G	H
1	ItemCode1	ItemCode4	ItemCode6	ItemCode7	ItemCode8	ItemCode9	ItemCode10	ItemCode11
2	ItemName1	ItemName4	ItemName6	ItemName7	ItemName8	ItemName9	ItemName10	ItemName11
3	ItemRate1	ItemRate4	ItemRate6	ItemRate7	ItemRate8	ItemRate9	ItemRate10	ItemRate11
4	ItemQuantity1	ItemQuantity4	ItemQuantity6	ItemQuantity7	ItemQuantity8	ItemQuantity9	ItemQuantity10	ItemQuantity11
5	ItemPrice1	ItemPrice4	ItemPrice6	ItemPrice7	ItemPrice8	ItemPrice9	ItemPrice10	ItemPrice11
6	ItemExpiry1	ItemExpiry4	ItemExpiry6	ItemExpiry7	ItemExpiry8	ItemExpiry9	ItemExpiry10	ItemExpiry11
7	ItemCode2	ItemCode5	ItemCode7	ItemCode8	ItemCode9	ItemCode10	ItemCode11	ItemCode12
8	ItemName2	ItemName5	ItemName7	ItemName8	ItemName9	ItemName10	ItemName11	ItemName12
9	ItemRate2	ItemRate5	ItemRate7	ItemRate8	ItemRate9	ItemRate10	ItemRate11	ItemRate12
10	ItemQuantity2	ItemQuantity5	ItemQuantity7	ItemQuantity8	ItemQuantity9	ItemQuantity10	ItemQuantity11	ItemQuantity12
11	ItemPrice2	ItemPrice5	ItemPrice7	ItemPrice8	ItemPrice9	ItemPrice10	ItemPrice11	ItemPrice12
12	ItemExpiry2	ItemExpiry5	ItemExpiry7	ItemExpiry8	ItemExpiry9	ItemExpiry10	ItemExpiry11	ItemExpiry12
13	ItemCode3
14	ItemName3
15	ItemRate3
16	ItemQuantity3
17	ItemPrice3
18	ItemExpiry3
19
20
21
22
23
24
25
26
27
28
29
30
31
32
Sheet1

Book1
	AI
1	CountItemsatColumns
2	0
3	18
4	30
5	42
6	54
7	66
8	78
9	90
10	102
11	102
12	102
13	102
14	102
15	102
16	102
17	102
18	102
19	102
20	102
21	102
22	102
23	102
24	102
25	102
26	102
27	102
28	102
29	102
30	102
31	102
32	102
Sheet1

Cell Formulas
Range	Formula
AI3:AI32	AI3	=IFERROR(COUNTIFS(INDIRECT(ADDRESS(1,ROWS($A$1:$A$1))&":"&ADDRESS(1000,ROWS($A$1:A1))),"<>"),0)

Then At Sheet2 Try this formula:

Book1
	A	B	C	D	E	F	G
1	Codes	Names	Rate	Quantity	Price	Expiry
2	ItemCode1	ItemName1	ItemRate1	ItemQuantity1	ItemPrice1	ItemExpiry1
3	ItemCode2	ItemName2	ItemRate2	ItemQuantity2	ItemPrice2	ItemExpiry2
4	ItemCode3	ItemName3	ItemRate3	ItemQuantity3	ItemPrice3	ItemExpiry3
5	ItemCode4	ItemName4	ItemRate4	ItemQuantity4	ItemPrice4	ItemExpiry4
6	ItemCode5	ItemName5	ItemRate5	ItemQuantity5	ItemPrice5	ItemExpiry5
7	ItemCode6	ItemName6	ItemRate6	ItemQuantity6	ItemPrice6	ItemExpiry6
8	ItemCode7	ItemName7	ItemRate7	ItemQuantity7	ItemPrice7	ItemExpiry7
9
10
Sheet2

Cell Formulas
Range	Formula
A2:F8	A2	=OFFSET(Sheet1!$A$1,(ROW($A1)-1)*6+COLUMNS($A:A)-1-INDEX(Sheet1!$AI$2:$AI$31,MATCH((ROW($A1)-1)*6+COLUMNS($A:A)-1,Sheet1!$AI$2:$AI$31,1)),MATCH((ROW($A1)-1)*6+COLUMNS($A:A)-1,Sheet1!$AI$2:$AI$31,1)-1)

Thanks! This worked fine!

 

[maabadi]

You're Welcome & Thanks for follow-up.