Convert Multiple Table Data to Individual Rows - MAC

[Kerny00]

Hi.

I have a large amount of data listed in rows. Each rows has 93 column headings and there are quantities listed in each column corresponding to the headings. For example:

Type Option1 Option2 Option3
SampleA 1 10 0
SampleB 100 5 3
SampleC 0 20 5

I would like to be able to breakdown the data and list is line by line as per below - one entry per line:

Type Option # Qty
SampleA Option1 1
SampleA Option2 10
SampleB Option1 100
SampleB Option2 5
SampleB Option3 3
SampleC Option2 20
SampleC Option3 5

I am using MS 2016 but it is on a MAC so Power Query cannot be used. I have hundreds of lines of data and 93 headings, so I can not do this manually.

Is there and option available to achieve this?

Thanks in advance. K

 

[NARAYANK991]

Hi ,

Why don't you upload a workbook which has adequate data in it ?

Narayan

 

[Peter Bartholomew]

I think such de-pivoting (to give PT normalised form) is a pretty standard operation that can be searched for. It can be achieved through the use of a formula or by VBA (Power Query is better but not available to you).

In the attached, it is possible to replace the helper columns in the output table by named formulas but this form shows the use of CEILING and MOD explicitly.

 

[bosco_yip]

Try.........

1] In J3, copied down :

=LOOKUP(ROWS($1:1),COUNTIF(OFFSET(B$2:D$2,,,ROW($1:$4),),">0")+1,A$3:A$5)&""

2] In K3, copied down :

=IF(J3="","",INDEX(B$2:D$2,AGGREGATE(15,6,COLUMN(A$1:C$1)/(INDEX(B$3:D$5,MATCH(J3,A$3:A$5,0),)>0),COUNTIF(J$3:J3,J3))))

3] In L3, copied down :

=IF(J3="","",INDEX(B$3:D$5,MATCH(J3,A$3:A$5,0),MATCH(K3,B$2:D$2,0)))

4] See attachment

Regards
Bosco

 

[Peter Bartholomew]

This is the same as above but without helper cells.
@Bosco's code also removes blank records [as I don't read direct cell references that will take me a little longer to work through!].

@Kerny00 Are these solutions addressing the correct problem? As @NARAYANK indicated, the problem is not completely defined.

 

[Kerny00]

Thanks all. I've attached a data sample.
Columns A-H are just information type columns and column H could be used as the 'key'.
The remaining columns hold the quantities relating to the heading (some are blank at this point but may have quantities in the future).

I would like a unique line for each qty listed on the row. On row 2 there are 3 items that have quantities. What I am chasing is that where there is a quanitity, a row is generated for that item and quantity only. I've added a tab called Data Output showing the result I am after. This is for Row 2 only.

Also, just to add, I have tried the Pivot Table option but it doesn't provide the data in an individual table, it just replicates the data in a table and is not the solution I am after.

Let me know if you can assist! Greatly Appreciated!!

 

[vletm]

Kerny00
Press [ Do It ]
Tested with MAC.

 

[Kerny00]

Thank you for that solution. It worked on the list I sent but I have loaded in more data and it takes so long to calculate. I need to be able to recalculate a new list every couple of weeks.

 

[vletm]

Kerny00
How many rows do You have nowadays?
Of course, more data would also mean longer time.
How long time it takes?
... more than couple of weeks?
Send 'better' amount of data ...