M
Mr BT
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,Product6,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,Product6,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,Product6,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,Product6,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,Product6,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 Find>Insert Entire Row macro is run for PEBKAC on the 3rd worksheet
From here I use Edit>Go To...>Special>Blanks (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,Product6,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
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,Product6,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,Product6,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,Product6,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,Product6,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,Product6,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 Find>Insert Entire Row macro is run for PEBKAC on the 3rd worksheet
From here I use Edit>Go To...>Special>Blanks (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,Product6,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