Transposing Data based on unique values

[smilie224]

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 A	Column B	Column C	Column D
Name	Field 1	Field 2	Field 3
John	Text1
John	Text2
John	Text3
Mary	NText1
Mary	NText2
Susan	YText1
Tim	ZText1
Tim	ZText2
Desired Data
Column A	Column B	Column C	Column D
Name	Field 1	Field 2	Field 3
John	Text1	Text2	Text3
Mary	NText1	NText2
Susan	YText1
Tim	ZText1	ZText2

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

 

[konew1]

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
	A	B	C	D	E
1	Current Data				*
2	*	*	*	*	*
3	Name	Field 1	Field 2	Field 3	*
4	John	Text1	*	*	*
5	John	Text2	*	*	*
6	John	Text3	*	*	*
7	Mary	NText1	*	*	*
8	Mary	NText2	*	*	*
9	Susan	YText1	*	*	*
10	Tim	ZText1	*	*	*
11	Tim	ZText2	*	*	*
12	*	*	*	*	*
13	Desired Data				*
14	*	*	1	2	3
15	Name	*	Field 1	Field 2	Field 3
16	John	*	Text1	Text2	Text3
17	Mary	*	NText1	NText2	*
18	Susan	*	YText1	*	*
19	Tim	*	ZText1	ZText2	*
20	*	*	*	*	*
Sheet6

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

 

[smilie224]

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]

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]

Try this. First test it on a copy of your data.

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

 

[smilie224]

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!

 

[AlphaFrog]

You're welcome. Thanks for the feedback.

 

[lightspeed]

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.

Thanks in advance for your advice.

Cheers,
LightSpeed

 

[AlphaFrog]

Try the code in post #5. I cleaned it up.

 

[lightspeed]

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!