Changing some rows to columns

[watsonk1]

Hi Everyone,

FYI, I do not think this is just a simple transpose question.

I have data in the form of:

Item Attribute
Item 1 XXX
Item 1 YYY
Item 1 ZZZ
Item 2 AAA
Item 2 BBB

etc.

I want to reformat my table so that it's like:

Item Attribute 1 Attribute 2 Attribute 3
Item 1 XXX YYY ZZZ
Item 2 AAA BBB

Each item has a variable number of Attributes (between 1 and about 20 of them), so I am looking at having that many columns, which would be fine. I tried to do this with a pivot table but it doesn't seem possible.

Any ideas? Thank you all!!

 

[Trouttrap2]

You can use a mega formula. I don't think transpose or pivot tables will yield a desired result.

First create a unique list for the formuala to lookup the Item in your range. Put that unique list in D2. Then put the formula in E2 and drag right then down. For this to work without adjusting the ranges your data should live in A1:B9.

The formula is an array formula. Use Ctrl+Shift+Enter, not just enter.
=IF(COLUMNS($E2:E2)<=COUNTIF($A$2:$A$9,$D2),INDEX($B$2:$B$9,SMALL(IF($A$2:$A$9=$D2,ROW($A$2:$A$9)-ROW($A$2)+1),COLUMNS($E2:E2))),"")

Sorry I can't give you an image.

 

[hiker95]

watsonk1,

Welcome to the MrExcel forum.

Here is a macro solution for you to consider that will run in the active worksheet, and, will adjust to the number of results columns needed.

Sample raw data, and, results:

Excel 2007
	A	B	C	D	E	F	G	H	I
1	Item	Attribute			Item	Attritube 1	Attritube 2	Attritube 3
2	Item 1	XXX			Item 1	XXX	YYY	ZZZ
3	Item 1	YYY			Item 2	AAA	BBB
4	Item 1	ZZZ
5	Item 2	AAA
6	Item 2	BBB
7
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub ReorgData()
' hiker95, 10/28/2016, ME973003
Dim rng As Range, r As Range
Dim v As Variant, o(), oMax As Long, n As Long
Set rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
With CreateObject("Scripting.Dictionary")
  .CompareMode = vbTextCompare
  For Each r In rng
    If Not .Exists(r.Value) Then
      n = n + 1
      ReDim Preserve o(1 To rng.Count, 1 To n)
      .Add r.Value, Array(2, n)
      o(1, n) = r.Value
      o(2, n) = r.Offset(, 1).Value
    Else
      v = .Item(r.Value)
      v(0) = v(0) + 1
      o(v(0), v(1)) = r.Offset(, 1).Value
      oMax = Application.Max(oMax, v(0))
      .Item(r.Value) = v
    End If
  Next r
  Range("E1").Resize(n, oMax) = Application.Transpose(o)
  With Range("F1").Resize(, oMax - 1)
    .Formula = "=""Attritube "" & Column() - 5"
    .Value = .Value
  End With
  Range("E1").Resize(n, oMax).Columns.AutoFit
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the ReorgData macro.

 

[watsonk1]

Hi Trouttrap,

Thank you for your help! My data here lives in A2:B7751. I created an Index of Column A values in D2:D4420. I adjusted the formula to say =IF(COLUMNS($E2:E2)<=COUNTIF($A$2:$A$7751,$D2),INDEX($B$2:$B$7751,SMALL(IF($A$2:$A$7751=$D2,ROW($A$2:$A$7751)-ROW($A$2)+1),COLUMNS($E2:E2))),"") and I put that in E2. I dragged over to Z, down to 4420, then Ctrl + Shift + Enter. It didn't work, though...almost all of the cells are blank, and a handful were populated by values from either Column A or D (I can't figure out which). I can't figure out how to fix it. I'm going to try the Macro posted below...I've never used a Macro before, but I'll let you know how it goes!

 

[hiker95]

I'm going to try the Macro posted below...I've never used a Macro before, but I'll let you know how it goes!

watsonk1,

I will be watching for your next reply.

 

[Trouttrap2]

Hi Trouttrap,

Thank you for your help! My data here lives in A2:B7751. I created an Index of Column A values in D2:D4420. I adjusted the formula to say =IF(COLUMNS($E2:E2)<=COUNTIF($A$2:$A$7751,$D2),INDEX($B$2:$B$7751,SMALL(IF($A$2:$A$7751=$D2,ROW($A$2:$A$7751)-ROW($A$2)+1),COLUMNS($E2:E2))),"") and I put that in E2. I dragged over to Z, down to 4420, then Ctrl + Shift + Enter. It didn't work, though...almost all of the cells are blank, and a handful were populated by values from either Column A or D (I can't figure out which). I can't figure out how to fix it. I'm going to try the Macro posted below...I've never used a Macro before, but I'll let you know how it goes!

Watsonk1,
that formula works for me. Are you entering the formula as an array formula? It should look like this in the formula bar.
{=IF(COLUMNS($E2:E2)<=COUNTIF($A$2:$A$7751,$D2),INDEX($B$2:$B$7751,SMALL(IF($A$2:$A$7751=$D2,ROW($A$2:$A$7751)-ROW($A$2)+1),COLUMNS($E2:E2))),"")}

 

[Trouttrap2]

Enter the formula first, then drag. I think that's where your steps are going wrong.

 

[watsonk1]

Trouttrap, you're right, this did work! Not sure exactly what I did wrong the first time.

Hiker95, I'll have to try that Macro next time!

Thank you both for taking the time to help me.

 

[hiker95]

Trouttrap, you're right, this did work! Not sure exactly what I did wrong the first time.

Hiker95, I'll have to try that Macro next time!

Thank you both for taking the time to help me.

watsonk1,

It is a shame that you did not try the macro, because, if you have a lot of data, the macro is the way to go, and, fast. And, your data does not have to grouped, or, sorted together per your original flat text display.

Thanks for the feedback.

You are very welcome. Glad we could help.

And, come back anytime.

 

[justmeok]

OMG hiker95 this is an amazing macro and almost exactly what I need! Could you be so kind as to modify it for my purposes? I currently have a maximum of 8 different attributes but that could increase in the future

My data is as follows

Item Attribute Amount
Item 1 XXX $15.00
Item 1 YYY $12.00
Item 1 ZZZ $10.00
Item 2 AAA $19.00
Item 2 BBB $22.00
Item 2 XXX $28.00

and what I want is
Item XXX YYY ZZZ AAA BBB
Item 1 $15.00 $12.00 $10.00
Item 2 $28.00 $19.00 $22.00

The above table looks a bit different in preview so I hope it makes sense to you. I did try and fiddle with the macro myself with an expected lack of success

Thank you and I hope it's ok with the forum rules to ask this? If not please feel free to ignore my request!