Looking for help in Creating new columns with duplicate unique data

[xchaserr]

Hi, I have a large excel file with a ton of customer information in it. The way it is formatted however leaves me scratching my head as to how to get the information into a usable format.
Basically each customer has a unique identifier, and of course phone numbers. For some reason the formatting has duplicate unique identifiers in rows with unique phone numbers a few columns over. Basically I would like to take the unique phone numbers and create new columns, phone1, phone2 etc.
Hopefully that makes sense, and thanks for any help.

Excel 2007 Win 7

Excel Workbook
	A	B	C	D	E	F
1	phCustID	phEmployeeID	phVendorID	phContactID	phType	phNumber
2	554				1	555-387-6457
3	1376				1	5553852998
4	1376				5	555477-8257
5	1381				1	5553127781
6	684				1	555-727-4991
7	774				1	555-399-3420
8	774				1	555-265-6718
9	1386				1	5554519580
10	609				1	555-528-4170
11	296				1	555-383-9822
12	807				1	555-457-6466
13	807				7	555-859-9588
14	807				5	555-456-0013
15	563				1	555-952-4443
16	563				1	555-312-7502
17	484				1	555-475-1010
18	484				1	555-755-4200
19	289				1	555-899-6914
20	289				1	555-258-1529
21	1393				1	5553935394
22	592				1	555-
23	592				7	555-365-4792
24	1396				1	5553783111
25	820				1	555-488-9271
26	1400				1	none
27	1401				1	none
28	1403				1	5553396064
29	625				1	555-482-7136
30	625				1	555-475-1046
TblPhone

Excel 2007

 

[Domenic]

Can you provide the actual expected results, based on the sample data?

 

[hiker95]

xchaserr,

Welcome to the MrExcel forum.


What would you want to do with duplicate phCustID's, that have different phType's?

Excel Workbook
	A	B	C	D	E	F
1	phCustID	phEmployeeID	phVendorID	phContactID	phType	phNumber
12	807				1	555-457-6466
13	807				7	555-859-9588
14	807				5	555-456-0013
TblPhone

Something like one of the following?

Excel Workbook
	A	B	C	D	E	F	G	H
1	phCustID	phEmployeeID	phVendorID	phContactID	phType	phNumber
2	807				1	555-457-6466	555-859-9588	555-456-0013
3
4	807				1,7,5	555-457-6466	555-859-9588	555-456-0013
Results

Can we have another screenshot where you have manually created the output you are looking for?

 

[xchaserr]

Hi thanks for the responses. Hiker95, thats exactly the output i was looking for. Sorry I'm not an excel guru so I tried to get my point across as best as possible. The different phtypes signify the type cell, home, work.

 

[hiker95]

xchaserr,


Sample raw data in worksheet TblPhone:

Excel Workbook
	A	B	C	D	E	F
1	phCustID	phEmployeeID	phVendorID	phContactID	phType	phNumber
2	289	289	289	289	1	555-899-6914
3	289	289	289	289	1	555-258-1529
4	296	296	296	296	1	555-383-9822
5	484	484	484	484	1	555-475-1010
6	484	484	484	484	1	555-755-4200
7	554	554	554	554	1	555-387-6457
8	563	563	563	563	1	555-952-4443
9	563	563	563	563	1	555-312-7502
10	592	592	592	592	1	555-
11	592	592	592	592	7	555-365-4792
12	609	609	609	609	1	555-528-4170
13	625	625	625	625	1	555-482-7136
14	625	625	625	625	1	555-475-1046
15	684	684	684	684	1	555-727-4991
16	774	774	774	774	1	555-399-3420
17	774	774	774	774	1	555-265-6718
18	807	807	807	807	1	555-457-6466
19	807	807	807	807	5	555-456-0013
20	807	807	807	807	7	555-859-9588
21	820	820	820	820	1	555-488-9271
22	1376	1376	1376	1376	1	5553852998
23	1376	1376	1376	1376	5	555477-8257
24	1381	1381	1381	1381	1	5553127781
25	1386	1386	1386	1386	1	5554519580
26	1393	1393	1393	1393	1	5553935394
27	1396	1396	1396	1396	1	5553783111
28	1400	1400	1400	1400	1	none
29	1401	1401	1401	1401	1	none
30	1403	1403	1403	1403	1	5553396064
31
TblPhone

After the macro in a new worksheet Results:

Excel Workbook
	A	B	C	D	E	F	G	H
1	phCustID	phEmployeeID	phVendorID	phContactID	phType	phone1	phone2	phone3
2	289	289	289	289	1, 1	555-899-6914	555-258-1529
3	296	296	296	296	1	555-383-9822
4	484	484	484	484	1, 1	555-475-1010	555-755-4200
5	554	554	554	554	1	555-387-6457
6	563	563	563	563	1, 1	555-952-4443	555-312-7502
7	592	592	592	592	1, 7	555-	555-365-4792
8	609	609	609	609	1	555-528-4170
9	625	625	625	625	1, 1	555-482-7136	555-475-1046
10	684	684	684	684	1	555-727-4991
11	774	774	774	774	1, 1	555-399-3420	555-265-6718
12	807	807	807	807	1, 5, 7	555-457-6466	555-456-0013	555-859-9588
13	820	820	820	820	1	555-488-9271
14	1376	1376	1376	1376	1, 5	5553852998	555477-8257
15	1381	1381	1381	1381	1	5553127781
16	1386	1386	1386	1386	1	5554519580
17	1393	1393	1393	1393	1	5553935394
18	1396	1396	1396	1396	1	5553783111
19	1400	1400	1400	1400	1	none
20	1401	1401	1401	1401	1	none
21	1403	1403	1403	1403	1	5553396064
22
Results

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub ReorgData()
' hiker95, 03/21/2011
' http://www.mrexcel.com/forum/showthread.php?t=537729
Dim w1 As Worksheet, wR As Worksheet
Dim LR As Long, a As Long, aa As Long, SR As Long, ER As Long, H As String, LC As Long
Application.ScreenUpdating = False
Set w1 = Worksheets("TblPhone")
If Not Evaluate("ISREF(Results!A1)") Then Worksheets.Add(After:=w1).Name = "Results"
Set wR = Worksheets("Results")
wR.UsedRange.Clear
Set wR = Worksheets("Results")
LR = w1.Cells(Rows.Count, 1).End(xlUp).Row
w1.Range("A2:F" & LR).Sort Key1:=w1.Range("A2"), Order1:=xlAscending, Key2:=w1.Range("E2") _
  , Order2:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
  Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal
w1.Columns(1).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=wR.Columns(1), Unique:=True
wR.Range("B1").Resize(, 4).Value = w1.Range("B1").Resize(, 4).Value
wR.Range("F1") = "phone1"
LR = wR.Cells(Rows.Count, 1).End(xlUp).Row
For a = 2 To LR Step 1
  H = ""
  SR = Application.Match(wR.Cells(a, 1), w1.Columns(1), 0)
  ER = Application.Match(wR.Cells(a, 1), w1.Columns(1), 1)
  wR.Range("B" & a).Resize(, 3).Value = w1.Range("B" & SR).Resize(, 3).Value
  For aa = SR To ER Step 1
    H = H & w1.Cells(aa, 5).Value & ", "
  Next aa
  If Right(H, 2) = ", " Then H = Left(H, Len(H) - 2)
  wR.Cells(a, 5) = H
  wR.Cells(a, 6).Resize(, ER - SR + 1).Value = Application.Transpose(w1.Range("F" & SR & ":F" & ER).Value)
Next a
LC = wR.Cells.Find("*", , xlValues, xlWhole, xlByColumns, xlPrevious, False).Column
For a = 7 To LC Step 1
  wR.Cells(1, a) = "phone" & a - 5
Next a
wR.Range(wR.Cells(2, 5), wR.Cells(LR, LC)).NumberFormat = "@"
wR.Range("E2:E" & LR).HorizontalAlignment = xlRight
wR.UsedRange.Columns.AutoFit
wR.Activate
Application.ScreenUpdating = True
End Sub

Then Save your workbook, Save As, a macro enabled workbook.


Then run the ReorgData macro.

 

[xchaserr]

Thank you very much, it worked perfectly.