Transposing Data based on unique values

smilie224

New Member
Joined
Nov 18, 2014
Messages
17
I am looking to transpose values from column B to columns B, C, D based on a unique value in column A. It seems it should be easy, but I have run into a lot of difficulty and have resulted to manually completing. In frustration, now I am trying here to see if anyone is available this weekend :)

I am using Excel for Mac 2011. Here is a sample of what I am hoping for.
The name column (A) varies in how many rows it has per person. I am just trying to locate the next unique name, copy/paste data from column B into either columns B, C, or D respectively.

All on the same worksheet. (Hoping my sample paste here will work)
<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 11pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { border-width: medium medium medium 1pt; border-style: none none none solid; border-color: -moz-use-text-color -moz-use-text-color -moz-use-text-color windowtext; }.xl64 { border-width: medium 1pt medium medium; border-style: none solid none none; border-color: -moz-use-text-color windowtext -moz-use-text-color -moz-use-text-color; }.xl65 { border-width: medium medium 1pt 1pt; border-style: none none solid solid; border-color: -moz-use-text-color -moz-use-text-color windowtext windowtext; }.xl66 { border-width: medium medium 1pt; border-style: none none solid; border-color: -moz-use-text-color -moz-use-text-color windowtext; }.xl67 { border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; }.xl68 { text-align: center; border-width: 1pt medium medium 1pt; border-style: solid none none solid; border-color: windowtext -moz-use-text-color -moz-use-text-color windowtext; background: rgb(217, 217, 217) none repeat scroll 0% 0%; }.xl69 { text-align: center; border-width: 1pt medium medium; border-style: solid none none; border-color: windowtext -moz-use-text-color -moz-use-text-color; background: rgb(217, 217, 217) none repeat scroll 0% 0%; }.xl70 { text-align: center; border-width: 1pt 1pt medium medium; border-style: solid solid none none; border-color: windowtext windowtext -moz-use-text-color -moz-use-text-color; background: rgb(217, 217, 217) none repeat scroll 0% 0%; }.xl71 { border-width: medium medium medium 1pt; border-style: none none none solid; border-color: -moz-use-text-color -moz-use-text-color -moz-use-text-color windowtext; background: rgb(216, 228, 188) none repeat scroll 0% 0%; }.xl72 { background: rgb(216, 228, 188) none repeat scroll 0% 0%; }.xl73 { border-width: medium 1pt medium medium; border-style: none solid none none; border-color: -moz-use-text-color windowtext -moz-use-text-color -moz-use-text-color; background: rgb(216, 228, 188) none repeat scroll 0% 0%; }.xl74 { text-align: center; border-width: medium medium medium 1pt; border-style: none none none solid; border-color: -moz-use-text-color -moz-use-text-color -moz-use-text-color windowtext; background: rgb(217, 217, 217) none repeat scroll 0% 0%; }.xl75 { text-align: center; background: rgb(217, 217, 217) none repeat scroll 0% 0%; }.xl76 { text-align: center; border-width: medium 1pt medium medium; border-style: none solid none none; border-color: -moz-use-text-color windowtext -moz-use-text-color -moz-use-text-color; background: rgb(217, 217, 217) none repeat scroll 0% 0%; }</style>
Current Data
Column AColumn BColumn CColumn D
NameField 1Field 2Field 3
JohnText1
JohnText2
JohnText3
MaryNText1
MaryNText2
SusanYText1
TimZText1
TimZText2
Desired Data
Column AColumn BColumn CColumn D
NameField 1Field 2Field 3
JohnText1Text2Text3
MaryNText1NText2
SusanYText1
TimZText1ZText2

<colgroup><col style="width:65pt" span="5" width="65"> </colgroup><tbody>
</tbody>
 
Last edited:

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

konew1

Well-known Member
Joined
Oct 17, 2007
Messages
2,287
Here is a formula approach. You need maintain a list of names at the destination range. I included a row numbering the 'fields". You could apply =COLUMNS() as an alternative. it assumes all lines for each person are grouped together.
Copy from C16 down and across.

Excel Workbook
ABCDE
1Current Data*
2*****
3NameField 1Field 2Field 3*
4JohnText1***
5JohnText2***
6JohnText3***
7MaryNText1***
8MaryNText2***
9SusanYText1***
10TimZText1***
11TimZText2***
12*****
13Desired Data*
14**123
15Name*Field 1Field 2Field 3
16John*Text1Text2Text3
17Mary*NText1NText2*
18Susan*YText1**
19Tim*ZText1ZText2*
20*****
Sheet6


EDIT: I don't know why Excel Genie sometimes puts "*" in empty cells. Ignore them
 
Last edited:

smilie224

New Member
Joined
Nov 18, 2014
Messages
17
Thank you KONew1, this will definitely work better than the manual moving if needed. However, it will mean I need to duplicate my current data (which is about 3,000 rows). The A13:E19 in my sample above are not actual fields that are already showing (specifically the column A in the desired set). Without that field there, then the formula is showing the "" as a result of the IF/THEN. Ideally, I was looking for VBA that would run the For/With/Next (I always confused them!) statements to loop some type of advanced filter.
Sorry if I was confusing in the paste of the mock spreadsheet. I wasn't sure how else to show it. Your formula DOES work, and again this is better than the manual way - so I will give it a shot tomorrow when I have fresh eyes on it if I don't see any code/advanced filter options, etc. posted. Thank you very much!
 

konew1

Well-known Member
Joined
Oct 17, 2007
Messages
2,287
OK. I know a vba solution will be appealing - that's why my first sentence stated that I had a formula solution.
To make this work you will need to copy the entire list of names to a new sheet, then (in excel 2007+) remove duplicates. Set up the header rows and copy paste the formula. If you have 3000 different names on the new sheet and more than 3 columns for the text fields, then there will be a lot of formula putting load on the response times.
Someone here will write code for you.
Cheers
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,261
Try this. First test it on a copy of your data.

VBA Code:
Sub Transposer()
    
    Dim v As Variant, r As Long, i As Long, c As Long
    
    v = ActiveSheet.UsedRange
    
    r = 2 'Start row
    c = 2: i = r
    Do
        r = r + 1
        If v(r, 1) = v(i, 1) Then
            c = c + 1
            If c > UBound(v, 2) Then ReDim Preserve v(1 To UBound(v, 1), 1 To c)
            v(i, c) = v(r, 2)
        Else
            c = 2
            i = i + 1
        End If
        If i <> r Then
            v(i, 1) = v(r, 1): v(r, 1) = ""
            v(i, 2) = v(r, 2): v(r, 2) = ""
        End If
    Loop Until r = UBound(v, 1)
    
    ActiveSheet.UsedRange.Resize(, UBound(v, 2)).Value = v
    
End Sub
 
Last edited:

smilie224

New Member
Joined
Nov 18, 2014
Messages
17
Wow, this was perfect! Thank you AlphaFrog. Now I just need to add my extraneous columns back in and re-write it slightly to fix all the excess.
Thank you both for your time! I always love learning both new functions and new macros - and you both taught me some. I can't thank you enough - particularly with my tight timeline to get this done. In much gratitude... smilie224! :rolleyes:
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,261
You're welcome. Thanks for the feedback.
 

lightspeed

New Member
Joined
Feb 4, 2020
Messages
3
Office Version
365
Platform
Windows
Hi AlphaFrog,

I am very interested with your VBA but I couldnt understand at all... LOL

I am attaching a test file below and hope you can guide me using the test file. What I wish to do is to transpose the data and have the distinct customer on Row and the product on the Column.
Testfile.JPG

Thanks in advance for your advice.

Cheers,
LightSpeed
 

lightspeed

New Member
Joined
Feb 4, 2020
Messages
3
Office Version
365
Platform
Windows
Oh My God, it works like magic :)
Thanks, AlphaFrog for spending time amending script #5 for me. It works wonders.

Cheers and have a good day ahead!
 

Watch MrExcel Video

Forum statistics

Threads
1,095,728
Messages
5,446,174
Members
405,389
Latest member
Excel n00b2

This Week's Hot Topics

Top