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

 

[watsonk1]

hiker95,

Thank you for your willingness to help. I was able to get it to run without the error message...for some reason 5000 rows was too many, but running it twice with 2500 rows worked just fine!

 

[fazraza81]

Hiker95,

Ok thank you, sorry for the confusion.

I have a new thread. the link is

http://www.mrexcel.com/forum/excel-...nto-columns-transpose-macro-codes-needed.html

Thanks!

 

[hiker95]

hiker95,

Thank you for your willingness to help. I was able to get it to run without the error message...for some reason 5000 rows was too many, but running it twice with 2500 rows worked just fine!

watsonk1,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.

 

[hiker95]

Hiker95,

Ok thank you, sorry for the confusion.

I have a new thread. the link is

http://www.mrexcel.com/forum/excel-...nto-columns-transpose-macro-codes-needed.html

Thanks!

fazraza81,

I see that your already have a macro solution for the above thread from rpaulson that works.

 

[fazraza81]

Hiker95,

yes i did, thanks so much for the follow up!

take care

 

[hiker95]

thanks so much for the follow up

fazraza81,

You are very welcome.

And, come back anytime.

 

[justmeok]

justmeok,

In reference to my reply #14, if you wanted the Attribute's sorted beginning in cell G1 (to the right), then let me know, and, I will adjust the macro.

Hi hiker95

Truly you are a genius and a lifesaver rolled into one This macro works exactly as I hoped and will save me so much time. I have no idea how or why it works but I love it! If you mean in the comment above to sort the attribute output to column headings in alpha order that would be an added bonus but certainly not a necessity

I cannot thank you enough for your help, it is very much appreciated!

 

[hiker95]

Hi hiker95

Truly you are a genius and a lifesaver rolled into one This macro works exactly as I hoped and will save me so much time. I have no idea how or why it works but I love it! If you mean in the comment above to sort the attribute output to column headings in alpha order that would be an added bonus but certainly not a necessity

I cannot thank you enough for your help, it is very much appreciated!

justmeok,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.

 

[hiker95]

If you mean in the comment above to sort the attribute output to column headings in alpha order that would be an added bonus but certainly not a necessity

justmeok,

Be back in a little while with an updated macro.

 

[hiker95]

justmeok,

With your raw data grouped/sorted in column A, by Item, then here is another macro solution for you to consider.

You can change the raw data worksheet name in the macro.

Sample raw data, and, new results:

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L
1	Item	Attribute	Amount			Item	AAA	BBB	XXX	YYY	ZZZ
2	Item 1	XXX	$15.00			Item 1			$15.00	$12.00	$10.00
3	Item 1	YYY	$12.00			Item 2	$19.00	$22.00	$28.00
4	Item 1	ZZZ	$10.00
5	Item 2	AAA	$19.00
6	Item 2	BBB	$22.00
7	Item 2	XXX	$28.00
8
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).

Sub ReorgData_justmeok_Sorted()
' hiker95, 11/05/2016, ME973003
Dim c As Range, lr As Long, nr As Long, nc As Long, lrg As Long
Dim i As Range, a As Range
Application.ScreenUpdating = False
With Sheets("Sheet1")   '<-- you can change the sheet name here
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  .Cells(1, 6).Value = .Cells(1, 1).Value
  .Columns("B:B").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Columns("G:G"), Unique:=True
  lrg = .Cells(Rows.Count, 7).End(xlUp).Row
  .Range("G2:G" & lrg).Sort key1:=.Range("G2"), order1:=1
  .Cells(1, 7).Resize(, lrg - 1).Value = Application.Transpose(.Range("G2:G" & lrg))
  .Cells(2, 7).Resize(lrg - 1).ClearContents
  For Each c In .Range("A2:A" & lr)
    Set a = .Columns(6).Find(c.Value, LookAt:=xlWhole)
    If a Is Nothing Then
      nr = .Cells(Rows.Count, 6).End(xlUp).Row + 1
      .Cells(nr, 6) = c.Value
    End If
    Set i = .Rows(1).Find(c.Offset(, 1).Value, LookAt:=xlWhole)
    If Not i Is Nothing Then
      With .Cells(nr, i.Column)
        .Value = c.Offset(, 2)
        .NumberFormat = "$#,##0.00"
      End With
    End If
  Next c
  .UsedRange.Columns.AutoFit
End With
Application.ScreenUpdating = True
End Sub

Then run the ReorgData_justmeok_Sorted macro.