PDA

View Full Version : If two cells match from any given row then...



jimbojones
08-07-2007, 06:25 AM
I don't really know how to summarise this problem, so I'm just going to try and explain it in as much detail as possible so somebody might be able to help me.

I have a spreadsheet with a number of sheets, the final sheet being "Summary" which gathers info from other sheets.

The sheet I need to get information has, amongst others, the following information:

A: PRODUCT CODE | B: DESPATCH DATE | C: FLAG
001...........................01/01/06..............0
001...........................05/01/06..............0
001...........................10/01/06..............1
002...........................01/02/06..............0
002...........................05/02/06..............0
002...........................10/02/06..............1

So for each product code there is numerous lines, one for each despatch date. The flag indicates the line for each product which is the LATEST dispatch date for that product. (Calculated using "today - despatch date"). This is just test data, there can be any number of rows per product code, not always three.

What I need is for the summary page, which only has ONE row per product code, to take the despatch date for each product code that is flagged as the latest despatch date.

So, I was thinking something that would search all the rows and say, "this cell = B[row number] IF A[row number] = 001 and C[row number] = 1". But I know that isn't the way to do it and I'm not sure how to do it.

Any ideas?

Bob Phillips
08-07-2007, 06:30 AM
=INDEX(B1:B6,MATCH(1,(A1:A6=1)*(C1:C6=1),0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter.Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually.When editing the formula, it must again be array-entered.

jimbojones
08-07-2007, 06:37 AM
Thanks. Unfortunately that didn't make a word of sense (I'm a bit of a noob, just a bit of vba knowledge). Also, in my spread sheet column B is actually column F and C is actually H, the columns in between contain irrelivant data. Don't know if that makes any difference.

Bob Phillips
08-07-2007, 06:48 AM
It's not VBA mate, not necessary as I understand it, but an Excel formula.

Just adjust the formula by those extra columns where required.

jimbojones
08-07-2007, 06:51 AM
Okay. Also, as I am looking in an array on another sheet, I need "sheet!" before the cells.

So if I type =INDEX(sheet!F1:F1000,MATCH(1,sheet!(A1:A1000=1)*sheet!(H1:H1000=1),0))

into the formula bar and then press ctrl+shift+enter it should work?

jimbojones
08-07-2007, 06:52 AM
I tried that, the cell now says #N/A

Bob Phillips
08-07-2007, 06:55 AM
Not quite

=INDEX(sheet!F1:F1000,MATCH(1,(sheet!A1:A1000=1)*(sheet!H1:H1000=1),0))

jimbojones
08-07-2007, 07:03 AM
#N/A again :(

Bob Phillips
08-07-2007, 07:12 AM
Is column A really text fileds with 001 in, or is it number filed with leading zeroes?

Bob Phillips
08-07-2007, 07:15 AM
I just tried it in those columns and two things occur. Is the data ona sheet called Sheet? Did you Ctrl-Shift-Enter?

jimbojones
08-07-2007, 07:16 AM
It's a "general" field because the real product codes have numbers, letters and underscores in.

But on my summary sheet, column A is the product code, so on my summary sheet, using the test data, it would be:

A: Product Code | B | C | D: Latest Despatch
001...............................This is the cell the formula is going in
002............................... As above

anandbohra
08-08-2007, 12:41 AM
better u post your workbook with few data & will check at our end
in the worksheet only describe what fields u want & from where they r comming