Summarising data from vertical to horizontal

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

12345
Company1

<tbody>
</tbody>
ProductAProductB
Company2

<tbody>
</tbody>
ProductB
Company3

<tbody>
</tbody>
ProductCProductB
Company4

<tbody>
</tbody>
ProductDProductB
Company5

<tbody>
</tbody>
ProductEProductB
Company6

<tbody>
</tbody>
ProductBProductDProductAProductJProductV
Company7

<tbody>
</tbody>
ProductAProductB

<tbody>
</tbody>


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

1234567
ProductACompany1Company6Company7
ProductBCompany1Company2Company6Company4Company3Company5Company7
ProductCCompany3
ProductDCompany4Company6
ProductECompany5
ProductJCompany6
ProductVCompany6

<tbody>
</tbody>

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

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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
1123451234567
2Company1ProductAProductBProductACompany1Company6Company7
3Company2ProductBProductBCompany1Company2Company3Company4Company5Company6Company7
4Company3ProductCProductBProductCCompany3
5Company4ProductDProductBProductDCompany4Company6
6Company5ProductEProductBProductECompany5
7Company6ProductBProductDProductAProductJProductVProductJCompany6
8Company7ProductAProductBProductVCompany6

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Array Formulas
CellFormula
I2{=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))),"")}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>



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

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?

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
1123451234567
2Company1ProductAProductBProductACompany1Company6Company7
3Company2ProductBProductBCompany1Company2Company3Company4Company5Company6Company7
4Company3ProductCProductBProductCCompany3
5Company4ProductDProductBProductDCompany4Company6
6Company5ProductEProductBProductECompany5
7Company6ProductBProductDProductAProductJProductVProductJCompany6
8Company7ProductAProductBProductVCompany6

<tbody>
</tbody>
Sheet1

Array Formulas
CellFormula
I2{=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))),"")}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>



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.

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

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

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