Transposing Data based on unique values

smilie224

New Member
Joined
Nov 18, 2014
Messages
19
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:

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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:
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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:
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,583
Messages
6,114,480
Members
448,574
Latest member
bestresearch

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top