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

 

[ask2tsp]

hiker95 your macro puts data from row 1 (partly) overwriting the header. Suggested small change below in red.
Creative solution! And another reason to use it is that you do not have to know the size of the result area.

Option Explicit


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("E2").Resize(n, oMax) = Application.Transpose(o)
  Range("E1") = "Item"
  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

 

[hiker95]

Re: Changing some rows to columns

hiker95 your macro puts data from row 1 (partly) overwriting the header. Suggested small change below in red.
Creative solution! And another reason to use it is that you do not have to know the size of the result area.

ask2tsp,

In the future, I would suggest that you actually try YOUR macro(s) before displaying screenshots(s), and/or, the macro code.

Sample raw data, and, the results of YOUR macro:

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K
1	Item	Attribute	Amount		Item	Attritube 1	Attritube 2	Attritube 3
2	Item 1	XXX	$15.00		Item	Attribute
3	Item 1	YYY	$12.00		Item 1	XXX	YYY	ZZZ
4	Item 1	ZZZ	$10.00		Item 2	AAA	BBB	XXX
5	Item 2	AAA	$19.00
6	Item 2	BBB	$22.00
7	Item 2	XXX	$28.00
8
Sheet1

Sub ReorgData_ask2tsp()
' ask2tsp, 10/29/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("E2").Resize(n, oMax) = Application.Transpose(o)
  Range("E1") = "Item"
  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

 

[hiker95]

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!

justmeok,

Be back in a little while with a new macro solution based on your flat text displays.

 

[hiker95]

justmeok,

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

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

Sample raw data, and, results in worksheet Sheet1:

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L
1	Item	Attribute	Amount			Item	XXX	YYY	ZZZ	AAA	BBB
2	Item 1	XXX	$15.00			Item 1	$15.00	$12.00	$10.00
3	Item 1	YYY	$12.00			Item 2	$28.00			$19.00	$22.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()
' hiker95, 10/29/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
  .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

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

 

[ask2tsp]

@hiker95: from post #1 I did not understand that "Item Attribute" was part of the sheet. That's what caused the confusion.

In the future, I would suggest that you actually try YOUR macro(s) before displaying screenshots(s), and/or, the macro code.

You assumed I did not try the macro. FYI I ALWAYS test extensively every bit of code, small or large, before publishing it. So to reply in your style

In the future, I would suggest that you don't feel offended so easy (see how harsh that sounds?)

 

[hiker95]

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.

 

[watsonk1]

Hi hiker95,

I tried your ReorgData macro a few times today but after a few minutes I keep getting run-time error 1004: "Application-defined or object-defined error." Any idea what is causing this?

Thank you!

 

[hiker95]

Hi hiker95,

I tried your ReorgData macro a few times today but after a few minutes I keep getting run-time error 1004: "Application-defined or object-defined error." Any idea what is causing this?

Thank you!

watsonk1,

In order to continue, and, so that I can get it right on the next try, I would like to see your actual raw data workbook/worksheet.

You can post your workbook/worksheet to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com

 

[fazraza81]

HIKER95,

I currently have data in B801-B22028. I need it to go from:

Data line 1
Data line 2
Date line 3
Date line 4
Date line 5
Date line 6
Date line 7

to:

Column C Column D Column E Column F Column G Column H Column I
Data line 1 Data line 2 Data line 3 Data line 4 Data line 5 Data line 6 Data line 7

Please help me with the right macro code.

Also, in between each data 'item' there is a blank row. So the 8th row is blank, then a new data 'item' starts again on the 9th row, for another 7 rows (including the 9th row), and so forth.

Please help! Thank you so much

 

[hiker95]

HIKER95,

I currently have data in B801-B22028. I need it to go from:

Data line 1
Data line 2
Date line 3
Date line 4
Date line 5
Date line 6
Date line 7

to:

Column C Column D Column E Column F Column G Column H Column I
Data line 1 Data line 2 Data line 3 Data line 4 Data line 5 Data line 6 Data line 7

Please help me with the right macro code.

Also, in between each data 'item' there is a blank row. So the 8th row is blank, then a new data 'item' starts again on the 9th row, for another 7 rows (including the 9th row), and so forth.

Please help! Thank you so much

fazraza81,

Because your raw data, and, results, are so different then any of the other replies in this thread:

Please start a new thread for your question and be sure to give it a search friendly title that accurately describes your need.


I would like more information. Please see the Forum Use Guidelines in the following link:

http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html


See reply #2 at the next link, if you want to show small screenshots, of the raw data, and, what the results should look like.

http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2507729


Or, you can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com


Then post a reply in this thread with a link to your new thread, and, I will have a look.