Complicated nested row formula - Multiple Range Compare

  • Thread starter OperationsNETTC15
  • Start date
O

OperationsNETTC15

Hey Everyone,

I have tried to glean as much as possible from the forums and/or Help, but
have hit a roadblock when it comes to my formula. I'll paste the specifics
below, but to summarize:

I am comparing 2 ranges from 2 different worksheets. One sheet has last
sale date information for each of our stores, the other sheet has inventory
transactions (tranfers, receipts, etc..) which I compare against the first.
For every item # match across sheets, I want to know the R1C1 format for the
corresponding dates. I.E if the item #'s match on Worksheet1$A$533, I don't
want the data in Worksheet1$E$533, I just want $E$533 (as a pure cell
reference).

For every instance I find a matching value from Worksheet1Column1 in
Worksheet2Column1, I want to return the cell reference for that location. I
initially used VLOOKUP, but it only gave me one return, when in some cases
there are many to be had. Here is my nested formula to get multiple returns:

{=IF(ISERROR(INDEX('Old Inventory'!$A$2:'Old Inventory'!$K$10000,
SMALL(IF('Old Inventory'!$A$2:'Old Inventory'!$A$10000=$A2,ROW('Old
Inventory'!$A$2:'Old Inventory'!$A$10000)), ROW(1:1)), 10)), "", INDEX('Old
Inventory'!$A$2:'Old Inventory'!$K$10000, SMALL(IF('Old Inventory'!$A$2:'Old
Inventory'!$A$10000=$A2, ROW('Old Inventory'!$A$2:'Old Inventory'!$A$10000)),
ROW(1:1)), 10))}

....the problem is that the ROW(1:1) will continually increment until I am at
the last row, which might be ROW(1536:1536). I need the Row returned to be
the one specifically applying to the row that matches ON THE OTHER SHEET, and
then the next one to be the next row that matches ON THE OTHER SHEET....like
I said, VLOOKUP was reliably giving me the first instance....but does not
work for me ultimately.

Worksheet1 example (I may add this is a filtered table...with many rows
filtered out):

SITEM Date Location Row Date Address
01637 10/1/2008 245 2 $D$2 $E$2
05797 9/29/2008 247 5 $D$5 $E$5
06032 10/31/2008215 6 $D$6
30574 7/17/2008 321 106 $D$106 $E$106
30574 10/31/2008215 107 $D$107
31697 7/28/2008 310 109 $D$109 $E$109
38873 9/29/2008 247 142 $D$142 $E$142
40321 4/4/2008 425 156 $D$156 $E$156
01883 7/23/2008 315 198 $D$198 $E$198
01883 9/10/2008 266 211 $D$211 $E$211
57515 3/6/2008 454 212 $D$212 $E$212
59777 9/16/2008 260 219 $D$219 $E$219


Worksheet 2:

HITM Date Location Inventory Date Last Sale Date Ref Days Old
01637 11/5/2007 245 'Old Inventory'!
01637 11/12/20070 'Old Inventory'!
01637 7/16/2008 0 'Old Inventory'!
01637 7/18/2008 353 'Old Inventory'!
01637 11/12/20070 'Old Inventory'!
01637 7/18/2008 0 'Old Inventory'!
01883 5/28/2009 266 'Old Inventory'!

I want to insert my formula into the Inventory Date column, which would
provide a cell reference for the next column to the right, which then
ultimately allows me to find out how old a specific item code is.

If you have read this far you have my thanks already....if you respond,
well, you rock.

Thanks.
 
V

Victor Delta

OperationsNETTC15 said:
Hey Everyone,

I have tried to glean as much as possible from the forums and/or Help, but
have hit a roadblock when it comes to my formula. I'll paste the
specifics
below, but to summarize:

I am comparing 2 ranges from 2 different worksheets. One sheet has last
sale date information for each of our stores, the other sheet has
inventory
transactions (tranfers, receipts, etc..) which I compare against the
first.
For every item # match across sheets, I want to know the R1C1 format for
the
corresponding dates. I.E if the item #'s match on Worksheet1$A$533, I
don't
want the data in Worksheet1$E$533, I just want $E$533 (as a pure cell
reference).

For every instance I find a matching value from Worksheet1Column1 in
Worksheet2Column1, I want to return the cell reference for that location.
I
initially used VLOOKUP, but it only gave me one return, when in some cases
there are many to be had. Here is my nested formula to get multiple
returns:

{=IF(ISERROR(INDEX('Old Inventory'!$A$2:'Old Inventory'!$K$10000,
SMALL(IF('Old Inventory'!$A$2:'Old Inventory'!$A$10000=$A2,ROW('Old
Inventory'!$A$2:'Old Inventory'!$A$10000)), ROW(1:1)), 10)), "",
INDEX('Old
Inventory'!$A$2:'Old Inventory'!$K$10000, SMALL(IF('Old
Inventory'!$A$2:'Old
Inventory'!$A$10000=$A2, ROW('Old Inventory'!$A$2:'Old
Inventory'!$A$10000)),
ROW(1:1)), 10))}

...the problem is that the ROW(1:1) will continually increment until I am
at
the last row, which might be ROW(1536:1536). I need the Row returned to
be
the one specifically applying to the row that matches ON THE OTHER SHEET,
and
then the next one to be the next row that matches ON THE OTHER
SHEET....like
I said, VLOOKUP was reliably giving me the first instance....but does not
work for me ultimately.

Worksheet1 example (I may add this is a filtered table...with many rows
filtered out):

SITEM Date Location Row Date Address
01637 10/1/2008 245 2 $D$2 $E$2
05797 9/29/2008 247 5 $D$5 $E$5
06032 10/31/2008215 6 $D$6
30574 7/17/2008 321 106 $D$106 $E$106
30574 10/31/2008215 107 $D$107
31697 7/28/2008 310 109 $D$109 $E$109
38873 9/29/2008 247 142 $D$142 $E$142
40321 4/4/2008 425 156 $D$156 $E$156
01883 7/23/2008 315 198 $D$198 $E$198
01883 9/10/2008 266 211 $D$211 $E$211
57515 3/6/2008 454 212 $D$212 $E$212
59777 9/16/2008 260 219 $D$219 $E$219


Worksheet 2:

HITM Date Location Inventory Date Last Sale Date Ref Days Old
01637 11/5/2007 245 'Old Inventory'!
01637 11/12/20070 'Old Inventory'!
01637 7/16/2008 0 'Old Inventory'!
01637 7/18/2008 353 'Old Inventory'!
01637 11/12/20070 'Old Inventory'!
01637 7/18/2008 0 'Old Inventory'!
01883 5/28/2009 266 'Old Inventory'!

I want to insert my formula into the Inventory Date column, which would
provide a cell reference for the next column to the right, which then
ultimately allows me to find out how old a specific item code is.

If you have read this far you have my thanks already....if you respond,
well, you rock.

Thanks.
Click to expand...

I'm not sure I fully understand what you are trying to do. However, if I've
got it right - you are trying to find the date corresponding to matches in
Col 1s of two worksheets, I would take a different approach (I like to try
and keep things simple if possible).

Why not use conditional formatting to highlight or colour the identical
cells on both sheets. If you prefer, you could colour the corresponding
dates instead.

Hope this helps?

V
 
O

OperationsNETTC15

I'm not sure I fully understand what you are trying to do. However, if I've
got it right - you are trying to find the date corresponding to matches in
Col 1s of two worksheets, I would take a different approach (I like to try
and keep things simple if possible).

Why not use conditional formatting to highlight or colour the identical
cells on both sheets. If you prefer, you could colour the corresponding
dates instead.
Click to expand...

Thanks for the response.

Unfortunately I am able to find the date, but need the cell reference
returned (address of the cell), not the data inside it. That is a minor
problem, however, since my formula increments the Row values linearly when I
need them to return a relative row....to sum up:

If I am on row 915 of worksheet 2, my formula shows ROW(915:915). The data
it should match on worksheet 1 might be on row 153. Obviously since my
formula returns row 915 on worksheet 1, it totally misses the correct data.

Conditional highlighting might be something I'll try...but I still need to
return hard data that I can feed into a graph at the end of the day.
 

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

Attempting to compare and deduct values based on date codes 3
inventory Add, Cost, Sold, Sold price - formula 3
FORMULA NEEDED FOR MONTHS 2
Running tally comparing two columns in unsorted list 7
Conditional Format Formula for finding duplicates in a row 1
Nesting more than 7 IF statements (Excel 2003) 7
Vlookup Multiple Criteria - Please help 1
conditional formatting referencing 3 cells by row in a range 3

Top