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

There's other data (other columns) that need to be included, which really
isn't my concern as I can sort and mix them up later.
The issue is getting all the services and products to appear on one line for
each SalesCode
Currently I take the above information and spell out if statements to get
the Midway results below. I do replace the names with an "X" but I used the
names here to help you keep track of what I'm trying to do.



Midway Results,,,,,,,
,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
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,,,Service1,,,,,,
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,,


I add a column in A and fill out a formula that identifies the first line of
each SalesCode. I used "PEBKAC" for this strategy. It seemed somewhat
appropriate at the time.

Midway Results,,,,,,,,,,,,,,,,,,
,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

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,,,Service1,,,,

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

2nd, 3rd, 4th, 5th, and 6th lines are identified as "FALSE" as opposed to
"PEBKAC"


Midway Results,,,,,,,,,,,,,,,,,,
,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

2,Code101,SaleAgent1,CustomerA,6/30/08,AccountA,X,,,,,,,X,,,,,
3,Code101,SaleAgent1,CustomerA,6/30/08,AccountA,X,,,,,,,,X,,,,
4,Code101,SaleAgent1,CustomerA,6/30/08,AccountA,X,,,,,,,,,X,,,

5,Code102,SaleAgent4,CustomerB,6/30/08,AccountB,,,,X,,,,,,,X,,
6,Code102,SaleAgent4,CustomerB,6/30/08,AccountB,,,,X,,,X,,,,,,

7,Code103,SaleAgent6,CustomerC,6/30/08,AccountC,,,,,,X,X,,,,,,

8,Code104,SaleAgent7,CustomerD,6/30/08,AccountD,,X,,,,,,,X,,,,

9,Code105,SaleAgent8,CustomerE,6/30/08,AccountE,X,,,,,,,X,,,,,
10,Code105,SaleAgent8,CustomerE,6/30/08,AccountE,X,,,,,,,,X,,,,

11,Code106,SaleAgent10,CustomerF,6/30/08,AccountF,,,X,,,,,,,,X,,


A macro searches for PEBKAC and enters a row (repeated several times) above
the data found. This is repeated about a 1000 times (remember there could be
any where from 300 to 1000 records).





Another column is inserted in column A and determines the single lines of
data and the multiple lines of data. (multiple will have 20 in column A,
where the singles will have 2)
An Auto filter pastes the single lines to another worksheet

Midway Results,,,,,,,,,,,,,,,,,,
,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

7,Code103,SaleAgent6,CustomerC,6/30/08,AccountC,,,,,,X,X,,,,,,
8,Code104,SaleAgent7,CustomerD,6/30/08,AccountD,,X,,,,,,,X,,,,
11,Code106,SaleAgent10,CustomerF,6/30/08,AccountF,,,X,,,,,,,,X,,

The multiple lines will be pasted to a 3rd worksheet.

Midway Results,,,,,,,,,,,,,,,,,,
,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


2,Code101,SaleAgent1,CustomerA,6/30/08,AccountA,X,,,,,,,X,,,,,
3,Code101,SaleAgent1,CustomerA,6/30/08,AccountA,X,,,,,,,,X,,,,
4,Code101,SaleAgent1,CustomerA,6/30/08,AccountA,X,,,,,,,,,X,,,

5,Code102,SaleAgent4,CustomerB,6/30/08,AccountB,,,,X,,,,,,,X,,
6,Code102,SaleAgent4,CustomerB,6/30/08,AccountB,,,,X,,,X,,,,,,


9,Code105,SaleAgent8,CustomerE,6/30/08,AccountE,X,,,,,,,X,,,,,
10,Code105,SaleAgent8,CustomerE,6/30/08,AccountE,X,,,,,,,,X,,,,



Again a FindInsert Entire Row macro is run for PEBKAC on the 3rd worksheet
From here I use EditGo To...SpecialBlanks (i use =<one cell up)
This is repeated in a similar fashion to the PEBKAC Find, it will select 2,
3, 4, 5, 6 rows and use the GoTo... Blank menu option until they have all
been filled in
(because there are different SaleCode counts each week I have kept the
integer at 1000. Anything less returns an error as expected when it runs out
of cells to fill.)

Once I've done this on Worksheet 3, I count how many "X"s are on each row
for each SaleCode. The largest of each grouping would be identified as the
line I wanted to keep.

Worksheet 2 and Worksheet 3 data would then be added to a 4th worksheet
before copying to then end file. and looking like this

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

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.