Beyond the VLOOKUP Limitation: Extracting records after the 1st re

M

Morton Detwyler

I have an Excel file with 10k+ records. Sometimes there are the same serial
numbers consuming multiple parts on the same Service Start Date (SVC START
{column E} » ROW 3 & 4), or the same serial number consuming the same parts
on different days (» ROW 9 and 10; ROW 11 and 12; ROW 14 and 15), or a serial
number consuming only one part on a single day (ROW 5, ROW 6).

My goal is to prepare a report that shows the parts consumed by SERIAL
NUMBER {column A}, by SVC START (Service Start Date {column E}), by PART DESC
(Part Description {column C}).

Thanks to the experts for your time and guidance....


[COLUMN] [ A ] [ B ] [ C ] [ D ] [ E ]
[ F ]
SERIAL PART PART PART SVC SVC
[ROW#] NUMBER NUMBER DESCR QTY START END
=====================================================
[ 3 ] ABC 123 WIDGET 1 1 11/26/08 12/5/08
[ 4 ] ABC 456 WIDGET 2 1 11/26/08
12/5/08
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
[ 5 ] DEF 456 WIDGET 2 1 7/24/09
7/24/09
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
[ 6 ] GHI 789 WIDGET 3 1 8/12/09
8/19/09
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
[ 7 ] JKL 135 WIDGET 4 1 11/12/08
11/19/08
[ 8 ] JKL 579 WIDGET 5 1 11/12/08
11/19/08
[ 9 ] JKL 246 WIDGET 6 1 11/12/08
11/19/08
[ 10 ] JKL 246 WIDGET 6 1 11/14/08
11/21/08
[ 11 ] JKL 401 WIDGET 7 1 11/28/08
12/9/08
[ 12 ] JKL 401 WIDGET 7 1 12/2/08
12/10/08
[ 13 ] JKL 403 WIDGET 8 1 1/15/09
1/28/09
[ 14 ] JKL 391 WIDGET 9 1 2/3/09
2/27/09
[ 15 ] JKL 391 WIDGET 9 1 3/17/09
4/7/09
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
 
M

Ms-Exl-Learner

I don’t know whether this is what you are looking, since the explanation
about your data structure is perfect but the brief about the result you are
looking to do is very little.

Anyway try the below in your duplicate copy of your workbook.

Male the cells like the below

A1 cell
SERIAL NUMBER

B1 cell
PART NUMBER

C1 cell
PART DESCR

D1 Cell
PART QTY

E1 Cell
SVC START

F1 Cell
SVC END

Because in your example I am seeing that you made the SERIAL in A1 cell and
NUMBER in A2 cell. Like this you have made all the data. So restructure
your data like the above.

Now the Second Row will be empty. So delete the Second row. In your
example the Data Range is A1:A15 now it will be A1:A14.

Just place the cursor in A1 cell and select Data>>Pivot Table and Pivot C>>

Just place the cursor in A1 cell and select Data >Pivot Table & Pivot Chart
Report>Next> (now you can see that it’s automatically selects your data range
from A column to F column) click Next>>Layout>

Now in the Right Side you can see the Field Lists that is SERIAL NUMBER,
PART NUMBER, PART DESCR, PART QTY, SVC START & SVC END.

Now click and hold the SVC START field and drop it in ROW.
Click and hold the SERIAL NUMBER field and drop it in ROW.
Click and hold the PART NUMBER field and drop it in ROW.
Click and hold the SERIAL NUMBER field and drop it in DATA.
Do double click in SERIAL NUMBER which is available in DATA and select COUNT
and give OK.

Give OK>>Finish.

Now you will see a new sheet is created with SVC START, SERIAL NUMBER, PART
NUMBER & Total

Place the cursor in SVC START. Now the pivot table toolbar will appear in
your window. If you don’t see the pivot table toolbar the do right click you
can see an option SHOW PIVOT TABLE TOOLBAR and click it.

In Pivot Table Toolbar Click Pivot Table and you will see a list of drop
down list now unselect the Subtotals.

In Pivot Table Toolbar Click Pivot Table and you will see a list of drop
down list now unselect the Subtotals.

Like this place the cursor in SERIAL NUMBER, PART NUMBER & TOTAL and
unselect the Subtotals.

I hope that this is the result you have expected.

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


Morton Detwyler said:
I have an Excel file with 10k+ records. Sometimes there are the same serial
numbers consuming multiple parts on the same Service Start Date (SVC START
{column E} » ROW 3 & 4), or the same serial number consuming the same parts
on different days (» ROW 9 and 10; ROW 11 and 12; ROW 14 and 15), or a serial
number consuming only one part on a single day (ROW 5, ROW 6).

My goal is to prepare a report that shows the parts consumed by SERIAL
NUMBER {column A}, by SVC START (Service Start Date {column E}), by PART DESC
(Part Description {column C}).

Thanks to the experts for your time and guidance....


[COLUMN] [ A ] [ B ] [ C ] [ D ] [ E ]
[ F ]
SERIAL PART PART PART SVC SVC
[ROW#] NUMBER NUMBER DESCR QTY START END
=====================================================
[ 3 ] ABC 123 WIDGET 1 1 11/26/08 12/5/08
[ 4 ] ABC 456 WIDGET 2 1 11/26/08
12/5/08
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
[ 5 ] DEF 456 WIDGET 2 1 7/24/09
7/24/09
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
[ 6 ] GHI 789 WIDGET 3 1 8/12/09
8/19/09
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
[ 7 ] JKL 135 WIDGET 4 1 11/12/08
11/19/08
[ 8 ] JKL 579 WIDGET 5 1 11/12/08
11/19/08
[ 9 ] JKL 246 WIDGET 6 1 11/12/08
11/19/08
[ 10 ] JKL 246 WIDGET 6 1 11/14/08
11/21/08
[ 11 ] JKL 401 WIDGET 7 1 11/28/08
12/9/08
[ 12 ] JKL 401 WIDGET 7 1 12/2/08
12/10/08
[ 13 ] JKL 403 WIDGET 8 1 1/15/09
1/28/09
[ 14 ] JKL 391 WIDGET 9 1 2/3/09
2/27/09
[ 15 ] JKL 391 WIDGET 9 1 3/17/09
4/7/09
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
Click to expand...
 
M

Morton Detwyler

Ms-Exl-Learner;
I do apologize for the ambiquity of my required output - I see where that
did leave for some questions. I was using an array Indirect and Match
statement but my counts were off. Your answer was very helpful in that it got
me to thinking about the way my data was structured. I changed some things
and ended up using and array sum statement and I got what I needed - thanks
for your help!

Ms-Exl-Learner said:
I don’t know whether this is what you are looking, since the explanation
about your data structure is perfect but the brief about the result you are
looking to do is very little.

Anyway try the below in your duplicate copy of your workbook.

Male the cells like the below

A1 cell
SERIAL NUMBER

B1 cell
PART NUMBER

C1 cell
PART DESCR

D1 Cell
PART QTY

E1 Cell
SVC START

F1 Cell
SVC END

Because in your example I am seeing that you made the SERIAL in A1 cell and
NUMBER in A2 cell. Like this you have made all the data. So restructure
your data like the above.

Now the Second Row will be empty. So delete the Second row. In your
example the Data Range is A1:A15 now it will be A1:A14.

Just place the cursor in A1 cell and select Data>>Pivot Table and Pivot C>>

Just place the cursor in A1 cell and select Data >Pivot Table & Pivot Chart
Report>Next> (now you can see that it’s automatically selects your data range
from A column to F column) click Next>>Layout>

Now in the Right Side you can see the Field Lists that is SERIAL NUMBER,
PART NUMBER, PART DESCR, PART QTY, SVC START & SVC END.

Now click and hold the SVC START field and drop it in ROW.
Click and hold the SERIAL NUMBER field and drop it in ROW.
Click and hold the PART NUMBER field and drop it in ROW.
Click and hold the SERIAL NUMBER field and drop it in DATA.
Do double click in SERIAL NUMBER which is available in DATA and select COUNT
and give OK.

Give OK>>Finish.

Now you will see a new sheet is created with SVC START, SERIAL NUMBER, PART
NUMBER & Total

Place the cursor in SVC START. Now the pivot table toolbar will appear in
your window. If you don’t see the pivot table toolbar the do right click you
can see an option SHOW PIVOT TABLE TOOLBAR and click it.

In Pivot Table Toolbar Click Pivot Table and you will see a list of drop
down list now unselect the Subtotals.

In Pivot Table Toolbar Click Pivot Table and you will see a list of drop
down list now unselect the Subtotals.

Like this place the cursor in SERIAL NUMBER, PART NUMBER & TOTAL and
unselect the Subtotals.

I hope that this is the result you have expected.

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


Morton Detwyler said:
I have an Excel file with 10k+ records. Sometimes there are the same serial
numbers consuming multiple parts on the same Service Start Date (SVC START
{column E} » ROW 3 & 4), or the same serial number consuming the same parts
on different days (» ROW 9 and 10; ROW 11 and 12; ROW 14 and 15), or a serial
number consuming only one part on a single day (ROW 5, ROW 6).

My goal is to prepare a report that shows the parts consumed by SERIAL
NUMBER {column A}, by SVC START (Service Start Date {column E}), by PART DESC
(Part Description {column C}).

Thanks to the experts for your time and guidance....


[COLUMN] [ A ] [ B ] [ C ] [ D ] [ E ]
[ F ]
SERIAL PART PART PART SVC SVC
[ROW#] NUMBER NUMBER DESCR QTY START END
=====================================================
[ 3 ] ABC 123 WIDGET 1 1 11/26/08 12/5/08
[ 4 ] ABC 456 WIDGET 2 1 11/26/08
12/5/08
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
[ 5 ] DEF 456 WIDGET 2 1 7/24/09
7/24/09
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
[ 6 ] GHI 789 WIDGET 3 1 8/12/09
8/19/09
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
[ 7 ] JKL 135 WIDGET 4 1 11/12/08
11/19/08
[ 8 ] JKL 579 WIDGET 5 1 11/12/08
11/19/08
[ 9 ] JKL 246 WIDGET 6 1 11/12/08
11/19/08
[ 10 ] JKL 246 WIDGET 6 1 11/14/08
11/21/08
[ 11 ] JKL 401 WIDGET 7 1 11/28/08
12/9/08
[ 12 ] JKL 401 WIDGET 7 1 12/2/08
12/10/08
[ 13 ] JKL 403 WIDGET 8 1 1/15/09
1/28/09
[ 14 ] JKL 391 WIDGET 9 1 2/3/09
2/27/09
[ 15 ] JKL 391 WIDGET 9 1 3/17/09
4/7/09
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
Click to expand...
Click to expand...
 
M

Ms-Exl-Learner

You're welcome and thanks for feeding back...

--------------------
(Ms-Exl-Learner)
--------------------


Morton Detwyler said:
Ms-Exl-Learner;
I do apologize for the ambiquity of my required output - I see where that
did leave for some questions. I was using an array Indirect and Match
statement but my counts were off. Your answer was very helpful in that it got
me to thinking about the way my data was structured. I changed some things
and ended up using and array sum statement and I got what I needed - thanks
for your help!

Ms-Exl-Learner said:
I don’t know whether this is what you are looking, since the explanation
about your data structure is perfect but the brief about the result you are
looking to do is very little.

Anyway try the below in your duplicate copy of your workbook.

Male the cells like the below

A1 cell
SERIAL NUMBER

B1 cell
PART NUMBER

C1 cell
PART DESCR

D1 Cell
PART QTY

E1 Cell
SVC START

F1 Cell
SVC END

Because in your example I am seeing that you made the SERIAL in A1 cell and
NUMBER in A2 cell. Like this you have made all the data. So restructure
your data like the above.

Now the Second Row will be empty. So delete the Second row. In your
example the Data Range is A1:A15 now it will be A1:A14.

Just place the cursor in A1 cell and select Data>>Pivot Table and Pivot C>>

Just place the cursor in A1 cell and select Data >Pivot Table & Pivot Chart
Report>Next> (now you can see that it’s automatically selects your data range
from A column to F column) click Next>>Layout>

Now in the Right Side you can see the Field Lists that is SERIAL NUMBER,
PART NUMBER, PART DESCR, PART QTY, SVC START & SVC END.

Now click and hold the SVC START field and drop it in ROW.
Click and hold the SERIAL NUMBER field and drop it in ROW.
Click and hold the PART NUMBER field and drop it in ROW.
Click and hold the SERIAL NUMBER field and drop it in DATA.
Do double click in SERIAL NUMBER which is available in DATA and select COUNT
and give OK.

Give OK>>Finish.

Now you will see a new sheet is created with SVC START, SERIAL NUMBER, PART
NUMBER & Total

Place the cursor in SVC START. Now the pivot table toolbar will appear in
your window. If you don’t see the pivot table toolbar the do right click you
can see an option SHOW PIVOT TABLE TOOLBAR and click it.

In Pivot Table Toolbar Click Pivot Table and you will see a list of drop
down list now unselect the Subtotals.

In Pivot Table Toolbar Click Pivot Table and you will see a list of drop
down list now unselect the Subtotals.

Like this place the cursor in SERIAL NUMBER, PART NUMBER & TOTAL and
unselect the Subtotals.

I hope that this is the result you have expected.

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


Morton Detwyler said:
I have an Excel file with 10k+ records. Sometimes there are the same serial
numbers consuming multiple parts on the same Service Start Date (SVC START
{column E} » ROW 3 & 4), or the same serial number consuming the same parts
on different days (» ROW 9 and 10; ROW 11 and 12; ROW 14 and 15), or a serial
number consuming only one part on a single day (ROW 5, ROW 6).

My goal is to prepare a report that shows the parts consumed by SERIAL
NUMBER {column A}, by SVC START (Service Start Date {column E}), by PART DESC
(Part Description {column C}).

Thanks to the experts for your time and guidance....


[COLUMN] [ A ] [ B ] [ C ] [ D ] [ E ]
[ F ]
SERIAL PART PART PART SVC SVC
[ROW#] NUMBER NUMBER DESCR QTY START END
=====================================================
[ 3 ] ABC 123 WIDGET 1 1 11/26/08 12/5/08
[ 4 ] ABC 456 WIDGET 2 1 11/26/08
12/5/08
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
[ 5 ] DEF 456 WIDGET 2 1 7/24/09
7/24/09
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
[ 6 ] GHI 789 WIDGET 3 1 8/12/09
8/19/09
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
[ 7 ] JKL 135 WIDGET 4 1 11/12/08
11/19/08
[ 8 ] JKL 579 WIDGET 5 1 11/12/08
11/19/08
[ 9 ] JKL 246 WIDGET 6 1 11/12/08
11/19/08
[ 10 ] JKL 246 WIDGET 6 1 11/14/08
11/21/08
[ 11 ] JKL 401 WIDGET 7 1 11/28/08
12/9/08
[ 12 ] JKL 401 WIDGET 7 1 12/2/08
12/10/08
[ 13 ] JKL 403 WIDGET 8 1 1/15/09
1/28/09
[ 14 ] JKL 391 WIDGET 9 1 2/3/09
2/27/09
[ 15 ] JKL 391 WIDGET 9 1 3/17/09
4/7/09
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Eliminating cells with a zero 7
calculate percentage of pay based on days old 2
determining running counts 5
Match Multiple Criteria and Return values positioned above the zero 6
How do I find a match for three variables to determine the result? 2
Timesheet formula problem 3
How to retrieve Named Range name? 2
Complex 2 table formula 5

Top