Mr BT wrote:
I need to find a simpler way to extract some data from a file exported and
sent to me by my lead department at work.

I will let you know what I receive, what I've done to extract the data, and
what I'd like to do with it. There is a comma separated example pasted here
with columns and rows identified.
I'm using anywhere from Excel 97 to 2003 as I move from desk to desk on any
day of the week.

Simply put, the file I receive will have hundreds of customers and a
SaleCode for each customer, one Product (out of 6) sold for each customer
but any where from one to 5 Services will be listed. Each of the Services
will be on a separate line. It looks something like this (paste into Excel)

Received Data,,,,,,,
,A,B,C,D,E,F,G
1,SaleCode,SaleAgent,Customer,DeliveryDate,Account ,Product,Service
2,Code101,SaleAgent1,CustomerA,6/30/08,AccountA,Product1,Service2
3,Code101,SaleAgent1,CustomerA,6/30/08,AccountA,Product1,Service3
4,Code101,SaleAgent1,CustomerA,6/30/08,AccountA,Product1,Service4
5,Code102,SaleAgent4,CustomerB,6/30/08,AccountB,Product4,Service5
6,Code102,SaleAgent4,CustomerB,6/30/08,AccountB,Product4,Service6
7,Code103,SaleAgent6,CustomerC,6/30/08,AccountC,Product6,Service1
8,Code104,SaleAgent7,CustomerD,6/30/08,AccountD,Product 2,Service3
9,Code105,SaleAgent8,CustomerE,6/30/08,AccountE,Product1,Service2
10,Code105,SaleAgent8,CustomerE,6/30/08,AccountE,Product1,Service3
11,Code106,SaleAgent10,CustomerF,6/30/08,AccountF,Product3,Service5


<SNIP


Intended Result,,,,,,,,,,,,,,,,,,
,A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R
1,SaleCode,SaleAgent,Customer,DeliveryDate,Account ,Product1,Product2,Product3,Product4,Product5,Prod uct6,Service1,Service2,Service3,Service4,Service5, Data1,Data2
4,Code101,SaleAgent1,CustomerA,6/30/08,AccountA,X,,,,,,,X,X,X,,,
6,Code102,SaleAgent4,CustomerB,6/30/08,AccountB,,,,X,,,X,,,,X,,
7,Code103,SaleAgent6,CustomerC,6/30/08,AccountC,,,,,,X,X,,,,,,
8,Code104,SaleAgent7,CustomerD,6/30/08,AccountD,,X,,,,,,,X,,,,
10,Code105,SaleAgent8,CustomerE,6/30/08,AccountE,X,,,,,,,X,X,,,,
11,Code106,SaleAgent10,CustomerF,6/30/08,AccountF,,,X,,,,,,,,X,,


I want to find a more efficient way to find "merge" the "X" for each
SaleCode into one row.
Someone at my work said this should be done by "simply" using Vlookup. But
he didn't go into details and he rambles on a bit to incoherent jargon (very
techie language for me).

I hope this all made sense. I appreciate your help. I look forward to your
answers (and questions).

Mr BT




You could try a Pivot Table. I was able to get close with a few minutes of playing.

First, I copied A2:E11 to A12:E21 and moved G2:G11 to F12:F21.

,A,B,C,D,E,F
1,SaleCode,SaleAgent,Customer,DeliveryDate,Account ,Product
2,Code101,SaleAgent1,CustomerA,6/30/2008,AccountA,Product1
3,Code101,SaleAgent1,CustomerA,6/30/2008,AccountA,Product1
4,Code101,SaleAgent1,CustomerA,6/30/2008,AccountA,Product1
5,Code102,SaleAgent4,CustomerB,6/30/2008,AccountB,Product4
6,Code102,SaleAgent4,CustomerB,6/30/2008,AccountB,Product4
7,Code103,SaleAgent6,CustomerC,6/30/2008,AccountC,Product6
8,Code104,SaleAgent7,CustomerD,6/30/2008,AccountD,Product2
9,Code105,SaleAgent8,CustomerE,6/30/2008,AccountE,Product1
10,Code105,SaleAgent8,CustomerE,6/30/2008,AccountE,Product1
11,Code106,SaleAgent10,CustomerF,6/30/2008,AccountF,Product3
12,Code101,SaleAgent1,CustomerA,6/30/2008,AccountA,Service2
13,Code101,SaleAgent1,CustomerA,6/30/2008,AccountA,Service3
14,Code101,SaleAgent1,CustomerA,6/30/2008,AccountA,Service4
15,Code102,SaleAgent4,CustomerB,6/30/2008,AccountB,Service5
16,Code102,SaleAgent4,CustomerB,6/30/2008,AccountB,Service6
17,Code103,SaleAgent6,CustomerC,6/30/2008,AccountC,Service1
18,Code104,SaleAgent7,CustomerD,6/30/2008,AccountD,Service3
19,Code105,SaleAgent8,CustomerE,6/30/2008,AccountE,Service2
20,Code105,SaleAgent8,CustomerE,6/30/2008,AccountE,Service3
21,Code106,SaleAgent10,CustomerF,6/30/2008,AccountF,Service5


Create a Pivot Table using this data. Put SaleCode, SaleAgent, Customer,
DeliveryDate and Account as Row Fields, Product as Column Field and then Account
as Data Item. Turn off all of the SubTotals and I got the following:


,A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q
1,SaleCode,SaleAgent,Customer,DeliveryDate,Account ,Product1,Product2,Product3,Product4,Product6,Serv ice1,Service2,Service3,Service4,Service5,Service6, Grand
Total
2,Code101,SaleAgent1,CustomerA,6/30/2008,AccountA,3,,,,,,1,1,1,,,6
3,Code102,SaleAgent4,CustomerB,6/30/2008,AccountB,,,,2,,,,,,1,1,4
4,Code103,SaleAgent6,CustomerC,6/30/2008,AccountC,,,,,1,1,,,,,,2
5,Code104,SaleAgent7,CustomerD,6/30/2008,AccountD,,1,,,,,,1,,,,2
6,Code105,SaleAgent8,CustomerE,6/30/2008,AccountE,2,,,,,,1,1,,,,4
7,Code106,SaleAgent10,CustomerF,6/30/2008,AccountF,,,1,,,,,,,1,,2
8,Grand Total,,,,,5,1,1,2,1,1,2,3,1,2,1,20

Note that your data didn't include any Product 5, so the Pivot Table didn't show it.