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>

 

[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!

Sorry, I just realised the transposed product info is all the same. For example, it created 5 columns of PA products under Customer A. Any remedy to this?

Thanks in advance.

 

[Akuini]

Hi, lightspeed
If you already have Dynamic Array function in your Office 365, then try this ( it uses Unique Function):

Sub a856938a()
'https://www.mrexcel.com/board/threads/transposing-data-based-on-unique-values.856938/
Dim i As Long, j As Long, n As Long
Dim r As Range
Dim ary

Application.ScreenUpdating = False
Set r = Range("A1", Cells(Rows.Count, "A").End(xlUp))
va = r
p = 1
For i = 2 To UBound(va, 1)
    z = i + WorksheetFunction.CountIf(r, va(i, 1)) - 1 'case insensitive
    ary = WorksheetFunction.Unique(Range("B" & i & ":B" & z))
    p = p + 1
    Range("D" & p) = Range("A" & i)
    Range("E" & p).Resize(1, UBound(ary)) = Application.Transpose(ary)
    i = z
Next
Application.ScreenUpdating = True
End Sub

Example:

Book1
	A	B	C	D	E	F	G
1
2	A	Truman		A	Truman	Ridge	Axl
3	A	Truman		B	Isaac	Leandro
4	A	Truman		C	Giovanni	Rod
5	A	Ridge
6	A	Axl
7	B	Isaac
8	B	Leandro
9	B	Isaac
10	C	Giovanni
11	C	Rod
Sheet2