Summarising data from vertical to horizontal

J

JimboJones16

New Member
Joined
Nov 17, 2016
Messages
2
Hello,

I have a data set that looks like this:

In the first data table, the columns named 1-5 are essentially feedback from sales people on information for Companies 1-7 (but goes to around 220 Companies in my RL spreadsheet).

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD][TABLE="width: 72"]
<tbody>[TR]
[TD="class: xl67, width: 72"]Company1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]ProductA[/TD]
[TD]ProductB[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 72"]
<tbody>[TR]
[TD="class: xl67, width: 72"]Company2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]ProductB[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 72"]
<tbody>[TR]
[TD="class: xl67, width: 72"]Company3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]ProductC[/TD]
[TD]ProductB[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 72"]
<tbody>[TR]
[TD="class: xl67, width: 72"]Company4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]ProductD[/TD]
[TD]ProductB[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 72"]
<tbody>[TR]
[TD="class: xl67, width: 72"]Company5[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]ProductE[/TD]
[TD]ProductB[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 72"]
<tbody>[TR]
[TD="class: xl67, width: 72"]Company6[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]ProductB[/TD]
[TD]ProductD[/TD]
[TD]ProductA[/TD]
[TD]ProductJ[/TD]
[TD]ProductV[/TD]
[/TR]
[TR]
[TD][TABLE="width: 72"]
<tbody>[TR]
[TD="class: xl67, width: 72"]Company7[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]ProductA[/TD]
[TD]ProductB[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I'd like to automatically manipulate the data so that it looks something like the below, where the intelligence is then summarised by the original Product, and lists the Companies instead, albeit horizontally:

The data will need to accommodate going up to 15 feedback columns (one product is mentioned up to around 15 times).

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]ProductA[/TD]
[TD]Company1[/TD]
[TD]Company6[/TD]
[TD]Company7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ProductB[/TD]
[TD]Company1[/TD]
[TD]Company2[/TD]
[TD]Company6[/TD]
[TD]Company4[/TD]
[TD]Company3[/TD]
[TD]Company5[/TD]
[TD]Company7[/TD]
[/TR]
[TR]
[TD]ProductC[/TD]
[TD]Company3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ProductD[/TD]
[TD]Company4[/TD]
[TD]Company6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ProductE[/TD]
[TD]Company5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ProductJ[/TD]
[TD]Company6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ProductV[/TD]
[TD]Company6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I have pulled this data together for insight; the sequential alpha and numeric characters have no actual relation to the data flow or relationship. The data itself is actually text that includes spaces and hypens, as well as blanks as above.

I think that some combination of IF, INDEX and MATCH formula might work for this, but I'm still fairly new to them and need a quick turnaround on this! I'm also struggling due to the horizontal and vertical complexaties.

I am sure there is a simple formula to help me create the second data table? I already have a unique list of the 'Products', so just need a formula to fill in all of the approporaite Company insight in italics.

Thank you very much in advance.

Best regards,
JJ
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Sort by date Sort by votes
Does it have to be a formula?
This would probably be easier using VBA since your table is "left-justified" in terms of columns, ie the columns are building up from the left.
 
Last edited:
Upvote 0
Welcome to the forum.

Special-K99 has a point, this might be better handled via VBA. But if you want a formula, here's a complicated one:

ABCDEFGHIJKLMNO

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4[/TD]
[TD="bgcolor: #FAFAFA, align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4[/TD]
[TD="bgcolor: #FAFAFA, align: right"]5[/TD]
[TD="bgcolor: #FAFAFA, align: right"]6[/TD]
[TD="bgcolor: #FAFAFA, align: right"]7[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA"]Company1[/TD]
[TD="bgcolor: #FAFAFA"]ProductA[/TD]
[TD="bgcolor: #FAFAFA"]ProductB[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]ProductA[/TD]
[TD="bgcolor: #FAFAFA"]Company1[/TD]
[TD="bgcolor: #FAFAFA"]Company6[/TD]
[TD="bgcolor: #FAFAFA"]Company7[/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA"]Company2[/TD]
[TD="bgcolor: #FAFAFA"]ProductB[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]ProductB[/TD]
[TD="bgcolor: #FAFAFA"]Company1[/TD]
[TD="bgcolor: #FAFAFA"]Company2[/TD]
[TD="bgcolor: #FAFAFA"]Company3[/TD]
[TD="bgcolor: #FAFAFA"]Company4[/TD]
[TD="bgcolor: #FAFAFA"]Company5[/TD]
[TD="bgcolor: #FAFAFA"]Company6[/TD]
[TD="bgcolor: #FAFAFA"]Company7[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA"]Company3[/TD]
[TD="bgcolor: #FAFAFA"]ProductC[/TD]
[TD="bgcolor: #FAFAFA"]ProductB[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]ProductC[/TD]
[TD="bgcolor: #FAFAFA"]Company3[/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA"]Company4[/TD]
[TD="bgcolor: #FAFAFA"]ProductD[/TD]
[TD="bgcolor: #FAFAFA"]ProductB[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]ProductD[/TD]
[TD="bgcolor: #FAFAFA"]Company4[/TD]
[TD="bgcolor: #FAFAFA"]Company6[/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA"]Company5[/TD]
[TD="bgcolor: #FAFAFA"]ProductE[/TD]
[TD="bgcolor: #FAFAFA"]ProductB[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]ProductE[/TD]
[TD="bgcolor: #FAFAFA"]Company5[/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FAFAFA"]Company6[/TD]
[TD="bgcolor: #FAFAFA"]ProductB[/TD]
[TD="bgcolor: #FAFAFA"]ProductD[/TD]
[TD="bgcolor: #FAFAFA"]ProductA[/TD]
[TD="bgcolor: #FAFAFA"]ProductJ[/TD]
[TD="bgcolor: #FAFAFA"]ProductV[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]ProductJ[/TD]
[TD="bgcolor: #FAFAFA"]Company6[/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FAFAFA"]Company7[/TD]
[TD="bgcolor: #FAFAFA"]ProductA[/TD]
[TD="bgcolor: #FAFAFA"]ProductB[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]ProductV[/TD]
[TD="bgcolor: #FAFAFA"]Company6[/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]I2[/TH]
[TD="align: left"]{=IFERROR(INDEX($A$2:$A$8,SMALL(IFERROR(SQRT(MMULT(--($B$2:$F$8=$H2),TRANSPOSE(COLUMN($B$1:$F$1)^0))*ROW($A$2:$A$8)-ROW($A$2)+1)^2,FALSE),COLUMNS($I2:I2))),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



The formula in I2 is an array formula. Copy it to the formula bar, change the references to match your sheet, then confirm with Control+Shift+Enter. Then copy it down and to the right as needed.
 
Upvote 0
Special-K99 said:
Does it have to be a formula?
This would probably be easier using VBA since your table is "left-justified" in terms of columns, ie the columns are building up from the left.
Click to expand...

Hi Special-K99,

Thanks for your contribution.

Unfortunately I am not fluent and have used VBA minimal times; I mainly use formulas and haven't really hit a point up until now where I needed some counsel to transform and analyse data.

I'd very much welcome a VBA solution to help me learn and for wider understanding if you wish to help? I'm sure it will help others in future too?

Eric W said:
Welcome to the forum.

Special-K99 has a point, this might be better handled via VBA. But if you want a formula, here's a complicated one:

ABCDEFGHIJKLMNO

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4[/TD]
[TD="bgcolor: #FAFAFA, align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4[/TD]
[TD="bgcolor: #FAFAFA, align: right"]5[/TD]
[TD="bgcolor: #FAFAFA, align: right"]6[/TD]
[TD="bgcolor: #FAFAFA, align: right"]7[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA"]Company1[/TD]
[TD="bgcolor: #FAFAFA"]ProductA[/TD]
[TD="bgcolor: #FAFAFA"]ProductB[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]ProductA[/TD]
[TD="bgcolor: #FAFAFA"]Company1[/TD]
[TD="bgcolor: #FAFAFA"]Company6[/TD]
[TD="bgcolor: #FAFAFA"]Company7[/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA"]Company2[/TD]
[TD="bgcolor: #FAFAFA"]ProductB[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]ProductB[/TD]
[TD="bgcolor: #FAFAFA"]Company1[/TD]
[TD="bgcolor: #FAFAFA"]Company2[/TD]
[TD="bgcolor: #FAFAFA"]Company3[/TD]
[TD="bgcolor: #FAFAFA"]Company4[/TD]
[TD="bgcolor: #FAFAFA"]Company5[/TD]
[TD="bgcolor: #FAFAFA"]Company6[/TD]
[TD="bgcolor: #FAFAFA"]Company7[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA"]Company3[/TD]
[TD="bgcolor: #FAFAFA"]ProductC[/TD]
[TD="bgcolor: #FAFAFA"]ProductB[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]ProductC[/TD]
[TD="bgcolor: #FAFAFA"]Company3[/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA"]Company4[/TD]
[TD="bgcolor: #FAFAFA"]ProductD[/TD]
[TD="bgcolor: #FAFAFA"]ProductB[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]ProductD[/TD]
[TD="bgcolor: #FAFAFA"]Company4[/TD]
[TD="bgcolor: #FAFAFA"]Company6[/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA"]Company5[/TD]
[TD="bgcolor: #FAFAFA"]ProductE[/TD]
[TD="bgcolor: #FAFAFA"]ProductB[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]ProductE[/TD]
[TD="bgcolor: #FAFAFA"]Company5[/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FAFAFA"]Company6[/TD]
[TD="bgcolor: #FAFAFA"]ProductB[/TD]
[TD="bgcolor: #FAFAFA"]ProductD[/TD]
[TD="bgcolor: #FAFAFA"]ProductA[/TD]
[TD="bgcolor: #FAFAFA"]ProductJ[/TD]
[TD="bgcolor: #FAFAFA"]ProductV[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]ProductJ[/TD]
[TD="bgcolor: #FAFAFA"]Company6[/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FAFAFA"]Company7[/TD]
[TD="bgcolor: #FAFAFA"]ProductA[/TD]
[TD="bgcolor: #FAFAFA"]ProductB[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]ProductV[/TD]
[TD="bgcolor: #FAFAFA"]Company6[/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]I2[/TH]
[TD="align: left"]{=IFERROR(INDEX($A$2:$A$8,SMALL(IFERROR(SQRT(MMULT(--($B$2:$F$8=$H2),TRANSPOSE(COLUMN($B$1:$F$1)^0))*ROW($A$2:$A$8)-ROW($A$2)+1)^2,FALSE),COLUMNS($I2:I2))),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



The formula in I2 is an array formula. Copy it to the formula bar, change the references to match your sheet, then confirm with Control+Shift+Enter. Then copy it down and to the right as needed.
Click to expand...

Hi Eric W,

Thanks very much for your clear and speedy response.

Once I expanded the relevant arrays to match my data set it worked perfectly. I can't thank you enough for saving me many, many hours of work here. Undoubtedly others too once they find this via search engines.

I'd very much welcome seeing a VBA solution from anyone if they wish; as I am keen to expand my usage of VBA and see how this can be handled differently.

With many thanks,
JJ
 
Upvote 0
Hi, JJ,

First, here's a minor tweak to my formula:

=IFERROR(INDEX($A$2:$A$8,SMALL(IFERROR(1/(1/(MMULT(--($B$2:$F$8=$H2),TRANSPOSE(COLUMN($B$1:$F$1)^0))*(ROW($A$2:$A$8)-ROW($A$2)+1))),""),COLUMNS($I2:I2))),"")

It basically just replaces the SQR with 1/1/. If someone doesn't use headers, they should use this version, otherwise you really won't see a difference.

Second, if you'd like to try a VBA solution, here's one:

1) Open a copy of your workbook
2) Press Alt-F11 to open the VBA editor
3) From the menu, click Insert > Module
4) Paste the following code:
Code: 
Sub Rearrange()
Dim r As Long, c As Long, cmp As String, prd As String, MyProds As Object
Dim ShIn As Worksheet, ShOut As Worksheet, wk As Variant, x As Variant

' Define input sheet and output sheet
    Set ShIn = Worksheets("Sheet1")
    Set ShOut = Worksheets("Sheet2")
    Set MyProds = CreateObject("Scripting.Dictionary")
    
' Read the input table, storing the data in the dictionary, using the product as the key
    With ShIn
        For r = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
            c = 2
            cmp = .Cells(r, "A")
            While .Cells(r, c) <> ""
                prd = .Cells(r, c).Value
                MyProds.Item(prd) = MyProds.Item(prd) & "," & cmp
                c = c + 1
            Wend
        Next r
    End With
    
' Write the data to the output sheet
    With ShOut
        .Cells.ClearContents
        .Cells(1, "A") = "Product"
        For c = 2 To 16
            .Cells(1, c) = c - 1
        Next c
        
        r = 2
        
        For Each x In MyProds
            wk = Split(MyProds.Item(x), ",")
            .Cells(r, "A") = x
            For c = 1 To UBound(wk)
                .Cells(r, c + 1) = wk(c)
            Next c
            r = r + 1
        Next x
        
    End With
    
' Sort the table by product
    With ShOut.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("A2")
        .SetRange Range("A:P")
        .Header = xlYes
        .Apply
    End With
    
End Sub
5) Change the sheet names in red to match where your input table is, and where you want to put the output table. Anything on the output sheet will be deleted first, so don't leave something you need there.
6) You can run the procedure either by pressing F5, or by going back to Excel, opening the macro selector with Alt-F8, and selecting it and clicking Run.

I'm glad the formula worked for you, hope the macro does too! :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Extract data from txt file
Replies
5
Views
527
Mankum
M
D
  • Question Question
VBA Slicer SlicerItems Selected
Replies
0
Views
1K
ddander54
D
J
  • Question Question
Display all companies of a state, when the state is selected
Replies
3
Views
774
Eric W
Eric W
E
  • Question Question
Lookup data from Horizontal set of data
Replies
6
Views
994
sandy666
sandy666
A
  • Question Question
Extract first numbers from right in variable layout alphanumeric text
2
Replies
15
Views
1K
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,228
Messages
6,170,875
Members
452,363
Latest member
merico17

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top