large database with multiple rows

[Steve]

I was given a very large database (6500 rows) in excel format. I
need to change the way it is layed out. Column A lists customers,
column B is their phone number(s). Some customers have 2 phone
numbers so they are listed twice. (in 2 rows..1 row for Main phone
number and another with their shipping dept phone number)
I need each customer to be listed on once. (in 1 row) with their phone
numbers in different columns 1 labled MAIN and another labeled
SHIPPING. Maybe a Lookup formula ? Hopefully, the examples below
convey what I
mean

Currently

Cust number type Phone Number
123456 Main 111.222.3333
123456 Shipping 111.222.3334

Desired

Cust Niumber Main Phone Shipping Phone
123456 111.222.3333 111.222.3334


Thanks in advance, Steve

 

[Bernard Liengme]

I will assume Row 1 has the labels (Cust Number, Type, Phone number)
In C2 enter =IF(A3=A2,C3,IF(A2=A1,"X",""))
Copy down the column (quickest way is to double click the fill handle -
solid square in lower right corner of active cell)
Select column C; use Copy followed by Paste Special with Values specifies
Now you have the second phone number in C for the first of a pair, or an X
for the second of the pair, and a blank when no duplicated Cust No
Use Data | Filter | Auto Filter to select all the X's and delete them
Best wishes

 

[Max]

Try also this formulas play ..

Illustrated in this sample:
http://www.freefilehosting.net/download/3ac9d
Extract UniqueCust n Corresp phone nums.xls

Source data as posted assumed in cols A to C,
data from row2 down to say, row7000

Enter in G1:H1 the 2 labels: Main, Shipping
The 2 labels must be consistent with (ie match) the indications under col B
("type")

In E2:
=IF(A2="","",IF(COUNTIF(A$2:A2,A2)>1,"",ROW()))
Leave E1 blank

In F2:
=IF(ROWS($1:1)>COUNT(E:E),"",INDEX(A:A,SMALL(E:E,ROWS($1:1))))

Then place in G2, array-enter the formula by pressing CTRL+SHIFT+ENTER
instead of just pressing ENTER:
=IF($F2="","",IF(ISNA(MATCH(1,($A$2:$A$7000=$F2)*($B$2:$B$7000=G$1),0)),"",INDEX($C$2:$C$7000,MATCH(1,($A$2:$A$7000=$F2)*($B$2:$B$7000=G$1),0))))
Copy G2 to H2. Select E2:H2, copy down to cover the max expected extent of
source data, ie to H7000. Minimize or hide away col E. Col F will return the
unique listing of cust numbers from col A while cols G & H will return the
corresponding phone numbers for "Main" &/or "Shipping".