Articles

Automated Transpose

June 2, 2010 at 11:09:13
Specs: Windows 7

I would like the ability to automatically transpose data into columns (data source can be any number of cells long, but only has data in A, B for the source). Sample data:

A B
Bob 14
Bob apple
Alice orange
Fred 27
Fred 128
Fred grape

transposed to:
A B C D etc.
Bob 14 apple
Alice orange
Fred 27 128 grape

I appreciate any insights you may have into this issue.

Thanks in advance.


See More: Automated Transpose

Report •


#1
June 2, 2010 at 12:06:48

We all want something.

Is there a reason that you've told us what you want?


Report •

#2
June 2, 2010 at 13:02:24

That's the answer to a request for assistance? Really?

Report •

#3
June 2, 2010 at 13:38:42

Please point out your request for assistance.

All I see is a post telling us what you want.

Would you walk into someone's office at work and just blurt out what you want or would you say something like...

"Can you please help me?" or "Do you have a minute? I need some help."

Why should you act any differently in a on-line forum?

We're all just volunteers here, giving up our free time - for free - to answer questions. Post an actual request, maybe even throwing in the word "Please" and I'm sure you'll get a totally different reaction.

DerbyDad03
Office Forum Moderator


Report •

Related Solutions

#4
June 2, 2010 at 13:46:06

Hi,

That's the answer to a request for assistance? Really?

I couldn't see the request in your original post either.

Everyone who responds is volunteering their time, so a request rather than a bland want would be appreciated.

Regards


Report •

#5
June 2, 2010 at 13:50:13

Updated. Thanks for your feedback.

Report •

#6
June 2, 2010 at 14:52:49

Hi,

This is a macro that will transpose/consolidate the data.
It assumes that the names in column A, are as you showed them - i.e., names in column A are in groups.
If the names could be randomly placed in column A, then a different approach will be required.

The resulting data is placed in columns A, B, C, D etc. starting one row below the end of the original source data.

If you wanted the original data replaced, this could be done by adding some code to delete the original rows of data, or a different approach could be used e.g., putting the source data into a temporary array in the visual basic code and then sorting and saving it.

Anyway here is my offering - I suggest adding the code to a button embedded in the relevant worksheet.
From the Ribbon select Developer (If it's not visible go to the Office Button, select Excel options at the bottom and select the Popular tab and check the 'Show Developer tab in the Ribbon' box)

In Developer - Controls - select Insert and choose the button icon.
Draw the button on the worksheet
In the 'Assign Macro' dialog box select 'New'

In the code window that opens enter this:

Option Explicit

Private Sub Button1_Click()
Dim rngStart As Range
Dim rngEnd As Range
Dim rngCell As Range
Dim strName As String
Dim intColOffst As Integer
Dim intRowOffst As Integer

On Error GoTo ErrHnd

'set start of data in column A
Set rngStart = ActiveSheet.Range("A1")
'find end of data in column A
Set rngEnd = ActiveSheet.Range("A" & CStr(Application.Rows.Count)).End(xlUp)

'setup dummy saved name
strName = ""

'set initial row offset for transposed data
intRowOffst = rngEnd.Row

'go through the range
For Each rngCell In ActiveSheet.Range(rngStart, rngEnd)
    'test if name in column A is different
    If rngCell.Text <> strName Then
        'name is different
        'save new name
        strName = rngCell.Text
        'reset column offset
        intColOffst = 1
        'increment row offset
        intRowOffst = intRowOffst + 1
        'save name
        ActiveSheet.Range("A1").Offset(intRowOffst, 0).Value = _
                    rngCell.Value
        'save data
        ActiveSheet.Range("A1").Offset(intRowOffst, intColOffst).Value = _
                    rngCell.Offset(0, 1).Value
        'increment column offset
        intColOffst = intColOffst + 1
        Else
        'name is same
        'save data
        ActiveSheet.Range("A1").Offset(intRowOffst, intColOffst).Value = _
                    rngCell.Offset(0, 1).Value
        'increment column offset
        intColOffst = intColOffst + 1
    End If
Next rngCell
Exit Sub

'error handler
ErrHnd:
Err.Clear
End Sub

Note that Sub Button1_Click() and End Sub will already be present, so don't duplicate them. Option explicit goes before Sub Button1_Click().

Click Save from the Visual Basic Menu.

Alt+f11 takes you back to the main Excel window.

Right click the button and Edit the name to something meaningful.

As changes made by Macros cannot be undone with the Undo button, test this macro on a copy of your data. Always make a backup of your Workbook before running this macro. This code has only been tested on sample data, and it has not been tested in your environment, so test it on copies of your data to ensure that it works 'as expected'

After selecting any cell, the new command button should now respond to a click and run the macro.

Regards


Report •

#7
June 2, 2010 at 16:06:35

Here's a fun way which doesn't required any VBA.

The concept utilizes a technique that can be used to find the Nth occurrence of value in a list. (As we know, Excel's LOOKUP functions only find the first occurrence of each value in a list.)

OK, here we go...

1 - Put your table in B1:C7 as follows:

    A       B                      C
1          Name	    Item
2          Bob                  14
3          Bob                  apple
4          Alice                 orange
5          Fred                  27
6          Fred                  128
7          Fred                  grape

2 - In A2, enter this formula and drag it down to A7:

=B2&" "&COUNTIF($B$2:$B2,B2)

Your table will now look like this:

    A          B         C
1            Name       Item
2  Bob 1     Bob       14
3  Bob 2     Bob       apple
4  Alice 1   Alice      orange
5  Fred 1    Fred       27
6  Fred 2    Fred       128
7  Fred 3    Fred       grape

3 - Select B1:B7 and use:

Data...Filter...Advanced Filter...Copy to another location

Place A9 in the Copy to: field
Check the Unique Records Only Box

You should have this:

    A          B         C
1            Name       Item
2  Bob 1     Bob        14
3  Bob 2     Bob        apple
4  Alice 1   Alice       orange
5  Fred 1    Fred        27
6  Fred 2    Fred        128
7  Fred 3    Fred        grape
8
9  Name
10 Bob
11 Alice
12 Fred


Now for the fun part:

4 - In B10 enter this formula:

=VLOOKUP(INDIRECT("A"&ROW()) & " " &COLUMN()-1,$A$2:$C$7,3,0)

You should get 14 in B10.

5 - Drag the formula across to D10, and then drag B10:D10 down to B12:D12.

You should have:

    A          B         C       D
1            Name       Item
2  Bob 1     Bob        14
3  Bob 2     Bob        apple
4  Alice 1   Alice       orange
5  Fred 1    Fred        27
6  Fred 2    Fred        128
7  Fred 3    Fred        grape
8
9  Name
10 Bob        14        apple   #N/A
11 Alice      orange    #N/A    #N/A
12 Fred       27        128     Grape

Of course, we want to get rid of the #N/A errors so we need to change the formula in B10. (I didn't want to make it any more confusing, so I used the "simple" version of the formula earlier).

6 - The full version of the formula for B10 is:

=IF(ISNA
(VLOOKUP(INDIRECT("A"&ROW()) & " " &COLUMN()-1,$A$2:$C$7,3,0)),"",
VLOOKUP(INDIRECT("A"&ROW()) & " " &COLUMN()-1,$A$2:$C$7,3,0))

Drag this across and then down and you should have:

    A          B         C       D
1            Name       Item
2  Bob 1     Bob	14
3  Bob 2     Bob	apple
4  Alice 1   Alice        orange
5  Fred 1    Fred	27
6  Fred 2    Fred	128
7  Fred 3    Fred	grape
8
9  Name
10 Bob        14        apple
11 Alice      orange
12 Fred       27        128     grape

Obviously, if you place the initial formula anywhere else besides B10, you'll need to adjust the ROW() and COLUMN() offsets to match.

There, wasn't that fun?


Report •

#8
June 3, 2010 at 05:59:55

Works great. Thanks a lot!

Report •

#9
June 3, 2010 at 06:41:51

You didn't say which solution "works great", but we appreciate the feedback!

Come on back if you need any more help.


Report •

#10
June 3, 2010 at 06:46:08

I played around with the macro, but really like the functionality of the formula based one. No need to save as a macro-enabled workbook, nothing to load or agree to by having to enable content. Still playing with the macro a bit. Like the hands-off nature of it. Really win/win. I appreciate having two options.

Report •

#11
June 3, 2010 at 08:42:10

Follow-up:

Any insight you have to then place or sort the data into unique columns would be appreciated:

For example...sample output from above:
9 Name
10 Bob 14 apple
11 Alice orange
12 Fred 27 128 grape


that would then be formatted like:
9 Name
10 Bob 14 apple
11 Alice orange
12 Fred 27 128 grape
13 Joe 14 orange grape 46


(added Joe record just for refrence)
I've saved the output from the first transpose as values, eliminating the formulas in order to use it as input into the new sort I'd like to perform. I've messed with hlookups without success.

Thanks again,

Ryan

Thanks again.

This loses something in translation when it posts...Trying to figure out how to make a table in the post to make it easier to read. Basically sorted output is desired to have unique values in each column.


Report •


Ask Question