Tricky Transposing in Excel 2007

March 18, 2009 at 09:04:10
Specs: Windows XP
I need to do some tricky transposing in Excel.

I have 4 columns with many rows of data that looks something like this:

DATE NAME ITEM COST
3/1/09 Joe shirt $15
3/1/09 Joe pants $10
3/1/09 Joe shoes $25
3/3/09 Joe belt $5
3/3/09 Bill shirt $15
3/3/09 Bill pants $10
3/3/09 Bill shoes $25
3/3/09 Bill belt $5
3/9/09 Jim shirt $8
3/9/09 Jim pants $19
3/9/09 Jim shoes $35
3/9/09 Jim belt $10

and so on for many rows...

One column [ITEM] will be transposed to the header row. Easy enough. The result will look like this:

Name Date Shirt Pants Shoes Belt
Joe 3/1/09 $15 $10 $25 $5
Bill 3/3/09 $17 $12 $28 $7
Jim 3/9/09 $8 $19 $35 $10

and so on...


Is there a way to create a macro that can transpose the data from multiple columns to rows based on my example?

I currently have to scroll down the rows copying chunks of data and transposing to each new row. In some cases, I could have over 1,000 rows of data to transpose. The data I'm working with is actually laboratory analyses, but for simplicity I used the above as an example, the structure is the same.

Thanks.


See More: Tricky Transposing in Excel 2007

Report •


#1
March 18, 2009 at 10:24:01
Let me start by saying this a lot of the code below is hardcoded, which may not be the best practice. It's just to give you a idea of what could be done.

First, I put your example data in A1:D13.

Then I set up a range to accept the transposed data, as follows:

1 - I did an Advanced Filter (unique records only) copy to F1:F4 on the Names.

2 - Then I typed DATE into G1.

3 - Then I did an Advanced Filter (unique records only) copy to anywhere on the Items then did a Copy...Paste Special...Transpose to H1:K1 getting:

     F      G      H      I     J      K
1  NAME   DATE   shirt  pants  shoes  belt
2  Joe
3  Bill
4  Jim

Now that my table was set up, I ran the following code. Obviously you'll need to set up your own ranges, and maybe reference column heading cells instead of hardcoding "shirts", etc. The point is that the process works, you just need to adapt it to your data.

Sub TransposeData()
 For Each Guy In Range("F2:F4")
  With Range("B1:B13")
   Set c = .Find(Guy, LookIn:=xlValues)
     If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            Range("G" & Guy.Row) = c.Offset(0, -1)
            If c.Offset(0, 1) = "shirt" Then Range("H" & Guy.Row) = c.Offset(0, 2)
            If c.Offset(0, 1) = "pants" Then Range("I" & Guy.Row) = c.Offset(0, 2)
            If c.Offset(0, 1) = "shoes" Then Range("J" & Guy.Row) = c.Offset(0, 2)
            If c.Offset(0, 1) = "belt" Then Range("K" & Guy.Row) = c.Offset(0, 2)
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
     End If
  End With
 Next
End Sub


Report •

#2
March 18, 2009 at 11:11:37
Thanks for the quick response derby.

I got your code to work on my example. (First time I used the Advanced Filter. I like it.) Now I will modify the code to work with my lab data.

I'll keep you posted on my success (or failure).


Report •

#3
March 18, 2009 at 14:33:13
Got it to work!

One issue I have is that some of the cells have a custom format for the data. I want to be able to present the data in a table that shows a "<" symbol for some values, but the actual data in the cell needs to be a number value that can be graphed. I do this by formatting the cell like this:
"< "##,## 0.00

Example:
actual data in cell: 0.5
format of data: < 0.50 (as seen in the table)

The macro code transposes the data as: 0.5

I assume that the LookIn:=xlValues in the macro code is what is being searched then copied/pasted. Is there a way to transpose the data as both the value and the format? It must be the xlFormats possibly?

Thanks a heap!

Russell


Report •

Related Solutions

#4
March 18, 2009 at 19:13:02
re: I assume that the LookIn:=xlValues in the macro code is what is being searched then copied/pasted.

Wrong on both accounts. :)

1 - The .Find is searching B1:B13 for the Names found in F2:F4.
2 - Nothing is being copied/pasted.

The For-Next loop looks at each name in F2:F4 and uses .Find to find it in B1:B13. Once it is found, the Offset property is used to look at cells near it based on Offset(x rows, y Columns), with x being 0 so it's always looking in the same row.

The code then places the data from the Offset cells into cells that are Offset(0 Rows and y Columns) from the current name in F2:F4.

Setting cells equal to other cells is very different from the copy/paste method.

That said, you can certainly format a range using VBA code. This would really have nothing to do with what the code is doing with the values, but you'd end up with the same result.

Only you can set the correct range to be formatted:

Range("A1:F10").NumberFormat = """< ""##,## 0.00 "



Report •

#5
March 19, 2009 at 13:54:44
Silly me. Nowhere in the code was copy or paste. :) I see the "=" in the For Next loop.

I just don't know the syntax to VB well at all. I can sort of deduce what's going on from what little coding I did about a decade ago.

Ok. So I did get the transposing code to work, but I'm confused as to how to create the NumberFormat. Is that in a macro or VB module or can it be done in Excel 2007's Conditional Formatting? PLUS I need to change the number format based on a condition of the value in an adjacent cell, as in this example condition:

If Column B is <1 And Column C has a "U" Then change NumberFormat in Column B to """< ""#,##0.00 "

If Column B is >=1 And <=9.9 And Column C has a "U" Then change NumberFormat in Column B to """< ""#,### 0.0"

If Column B is >=10 And Column C has a "U" Then change NumberFormat in Column B to """< ""#,###"

If Column B does not contain a "U" Then format numbers as conditions above without "<".

One thing you said in your first email about hardcoding the name of the column ("shirt", "pants", etc.) or referencing column heading cells instead. That would make it easier as typing in chemical compounds like "1,1,1-Trichlorethene" or "cis-1,2-Dichloroethene" gets a bit tiresome. Using the column header would be nice, and more usable across different workbooks.

Thanks. You've been a great help.


Report •

#6
March 19, 2009 at 19:45:16
The line that sets the number format is used in a macro, which is placed in VBA module.

If you need to change the format based on a cell's content, you could use an If statement in your macro:

If Range("B1") < 1 And Range("C1") = "U" Then Range("B1").NumberFormat = """< ""#,##0.00 "

To use Column headings instead of hardcoded text, it might look something like this:

If c.Offset(0, 1) = Range("H1") Then Range("H" & Guy.Row) = c.Offset(0, 2)


Report •

#7
March 20, 2009 at 07:58:23
Thanks DerbyDad,

I seem to be having a problem with the IF statement macro. I copied it as you wrote with the format on Range B1 based on the "U" in C1, that worked fine. If I change it to look at B1:B10 based on the "U" in C1:C10, the macro blows up. I get a "Run-time error (13) Type mismatch". How do I get the NumberFormat to change based on the "U" for each cell in an entire column? It could be 10 cells or 1,000 cells in the column, it varies. Rather than hardcoding the exact number of cells, choosing an entire column would be more flexible.


Report •

#8
March 20, 2009 at 08:33:21
Loop through the IF's as follows:

For 1000 rows:

 For rw = 1 To 1000
  If Range("B" & rw) < 1 And Range("C" & rw) = "U" Then _
     Range("B" & rw).NumberFormat = """< ""#,##0.00 "
 Next

or

  For rw = 1 To 1000
   If Cells(rw, 2) < 1 And Cells(rw, 3) = "U" Then _
      Cells(rw, 2).NumberFormat = """< ""#,##0.00 "
  Next

For an entire column:

  For Each rw In Columns("B")
    If Range("B" & rw.Row) < 1 And Range("C" & rw.Row) = "U" Then _
       Range("B" & rw.Row).NumberFormat = """< ""#,##0.00 "
  Next


Gone for the weekend. See ya!


Report •

#9
March 20, 2009 at 08:41:08
Thanks! I'll play with this today. Have a good weekend. Spring has Sprung!


Report •

#10
March 21, 2009 at 11:36:19
So I got it to work, and added If..Then statements that formatted cells without the "U" flag for the "<" sign. I was wondering if there was a cleaner way to do this, like with Case or another kind of statement to condense the code a bit.

Sub Format()
    For rw = 1 To 1000
        
        'Format and add a "<" sign to any numbers in Range B
        'that has a "U" flag in Range C
        
        If Range("B" & rw) < 1 And Range("C" & rw) = "U" Then _
            Range("B" & rw).NumberFormat = """< ""#,##0.00 "
        If Range("B" & rw) > 10 And Range("C" & rw) = "U" Then _
            Range("B" & rw).NumberFormat = """< ""#,### "
        If Range("B" & rw) >= 1.1 And Range("B" & rw) <= 9.9 And _
                Range("C" & rw) = "U" Then _
                Range("B" & rw).NumberFormat = """< ""#,#0.0 "
          
        'Format numbers in Range B that has an empty cell in Range C
        
        If Range("B" & rw) < 1 And Range("C" & rw) = "" Then _
            Range("B" & rw).NumberFormat = "#,##0.00 "
        If Range("B" & rw) >= 1.1 And Range("B" & rw) <= 9.9 And _
            Range("C" & rw) = "" Then Range("B" & rw).NumberFormat = "#,#0.0 "
        If Range("B" & rw) > 10 And Range("C" & rw) = "" Then _
            Range("B" & rw).NumberFormat = "#,### "
    Next
  
End Sub

Before:		After:

Col B	Col C	Col B	Col C
0.05	U	< 0.05 	U
0.5	U	< 0.50 	U
5	U	< 5.0 	U
50	U	< 50 	U
500	U	< 500 	U
5000	U	< 5,000	U
0.05		0.05
0.5		0.50
5		5.0 
50		50 
500		500
5000		5,000


Report •

#11
March 23, 2009 at 06:28:36
I suppose you could condense the code using Select Case, but it might take some work.

AFAIK Select Case evaluates a single variable. Since you are checking for 2 conditions, you might have to combine the values in the 2 cells into a single variable and evaluate the "combination".

If you work it out, please share it with the rest of us.


Report •

#12
March 23, 2009 at 08:07:55
I'll give the Select Case a try sometime soon, for fun. For now the IF..Then statement works well.

I have another issue I discovered. After I change all the numbers to include "<" based on the Format() macro, then transpose the data based on the earlier TransposeData() macro, the "<" sign does not transpose since the value in the cells is a number and not a text value. Is there a way to run the macro to include the "<" sign, then transpose the data to the desired location with the "<" sign?

I still need the number to be a number for charting in a graph, so I don't want to change the cell value to text.


Report •

#13
March 23, 2009 at 09:59:02
I could be wrong, but my guess is that you are not getting the "<" is because the "new" cells are not formatted the same way.

If formatting the new cells after transposing the data is not convenient, then you might have to go with copy/paste instead of setting the new cells equal to the old ones. Then you could something like this:

Range("A5").PasteSpecial Paste:=xlPasteFormats

Why not transpose the data and then run the formatting code against the new range?

BTW - these don't have to be separate macros. You could format the original cells, transpose the data and format the new cells all in one macro. Just combine everything and make sure the segments are in the correct order.


Report •

#14
March 23, 2009 at 13:38:30
I have a whole other issue that popped up while transposing. It's rather confusing to explain without the actual Excel workbook to view, but I'll try.

In the Range we initially called "Guy" I have values that are causing the transposing macro to place the wrong data into the cells.

(Note that my actual table is monitoring wells in the "Guy" range. So I have MW-1, MW-2, MW-3,...MW-10, then MW-1D, MW-2D, MW-3D,...MW-10D.)

When I run that TransposeData() macro I can see the values entering into the cells changing before moving to the next row. Like it's scrolling through data. The data in the MW-1 row actually has data from the MW-1D row.

Using the original example we used for setting up this macro it's like data transposed from row "Bill" was replaced with data from row "Billy".

Is it because MW-1 and MW-1D have similar characters and although unique, not unique enough for the macro to differentiate?

I tested it by changing the .Find from Range B1:B2000 to B1:B575, as follows (note, "Sample_ID" replaced "Guy"):

For Each Sample_ID In Range("F2:F27")
With Range("B1:B575")
Set c = .Find(Sample_ID, LookIn:=xlValues)

"B575" stops just before the cell Name "MW-1D". The data transposes perfectly at that Range. If I change to "B576" and higher, the values change. "MW-1" values now have values from "MW-1D".

Just when I thought I had this licked...

Hopefully my explanation is comprehensible and fixable.

Last note: just to make it more complicated I have well names of MW-1, MW-1D, MW-11, and MW-10. Also repeated are MW-2/MW-2d, MW-3/MW-3d, and so on. The data values in these other cells are all over the place as well.


Report •

#15
March 23, 2009 at 15:08:33
Try adding LookAt:=xlWhole

In the VBA editor place your cursor on Find and hit F1.

Read the Help file to see what options are available.


Report •

#16
March 23, 2009 at 17:51:58
derbydad: Try adding LookAt:=xlWhole

It worked!

Changed to:
"Set c = .Find(Sample_ID, LookIn:=xlValues, LookAt:=xlWhole)"

It found all the whole values and transposed them in the correct order.

derbydad: Why not transpose the data and then run the formatting code against the new range?

I'm working on this part next.

Thanks DerbyDad!


Report •

#17
March 25, 2009 at 10:33:14
So my issue with converting the data after transposing is that I still need to have the "<" sign for some cells based on whether or not there is a "U" sign in an adjacent cell. The "Format()" works great as it checks down a column, but now that the data is transposed I can't figure out how to re-format each transposed cell in rows by checking for the "U" in a column. It seems that your suggestion of copying and pasting the cells after formatting might be easier, thus taking the format with the data. I'm not sure how to setup the TransposeData() macro to cut/paste versus make the cells equal.

In need to add/modify this (from Response Number 13):

<Some copy function here>
Range("A5").PasteSpecial Paste:=xlPasteFormats

to this (from Response Number 1):

If c.Offset(0, 1) = "shirt" Then Range("H" & Guy.Row) = c.Offset(0, 2)


Report •


Ask Question