large database with multiple rows
 

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

large database with multiple rows
 

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


--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Steve" wrote in message
...
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

large database with multiple rows
 

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,R OWS($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".
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Steve" wrote in message
...
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