• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How do I find the value of the first occurence of a transaction

M

melaniec

New Member
Hi


If in my first column (say A) I have multiple listings of a transaction number, yet in column B I only have occasional listings of a PO number, how can I get Excel to look up the transaction number and return the PO number on the assumption that the PO may not necessarily be the first item listed. For example


Column A Column B

123 (blank)

123 (blank)

123 (blank)

123 CB456

123 (blank)

123 (blank)


In this example, I need Excel to essentially do something equivalent to a vlookup and lookup tran number 123 and return the value CB456. However, obviously vlookup would only return zero as the first time it encounters tran 123 it has a blank in column B.


I keep thinking it can't be that hard and there must be some sort of formula that says "Look up this tran number, and give me the number in column B the first time you see anything >0".


Please help as something so simple is becoming frustrating!


Mel
 
Hi Mel ,


Try this :


=INDEX(B2:B10,MATCH(1,((A2:A10=123)*(B2:B10<>"")),0))


entered as an array formula , using CTRL SHIFT ENTER.


I have assumed that your data range is A2:B10.


Narayan
 
Looks like it will work in theory. Thanks so much I'll try it at home where I have my spreadie. I just tried it out on a dummy spreadsheet at work and it seems to be returning #N/A.


Sorry to sound like a numpty but what do you mean by entering it as an array formula with C, S and E?
 
Hi Mel,


Welcome to the forum and we glad to have you here.


Try this:


==INDEX(B1:B6,SMALL(IF((A1:A6=123)*(B1:B6<>""),ROW(A1:A6),""),ROW(A1))) press as CTRL+SHIFT+ENTER (not just enter) to enter it as an array formula


I have assumed your data range is A1:B6


Note: Instead of writing 123 directly in the formula, you write the value some where in a cell and pass that cell reference to the formula as well.


Kaushik
 
Hi Kaushik


I tried yours and it didn't work as it says there are too many arguments. I will be stating within my formula =(cell value) rather than 123 but that's easily amended.


Thanks anyway
 
Hi Narayan, sorry tried yours again using Control Shift Enter instead of just enter and it said the formula had an error too.
 
Hi Mel ,


I tried out my formula with the following data :

[pre]
Code: 
123
123
123
123
112	CE234
123	CB456
124	CF112
11	CG132
123
[/pre]
and it displayed CB456. Can you check your data and see whether the 123 which I have explicitly used in the formula is not text ?


Narayan
 
Hi Mel ,


SpeedyShare does not require you to register either to upload or to download files.


If you click on the link posted by Kaushik , you will see the following on the screen which is displayed :


Download: sample.xlsx 7.61 KB


Right click on the file name and save it to your computer.


Narayan
 
Ah right, thanks guys. I think I need to have a go at home as when I right click at work no 'download file' option comes up, which is probably to do with the firewall.
 
Narayank


Thanks so much. Managed to get your formula to work on my work PC. I was being a bit thick actually...firstly I hadn't been using the Control Shift Enter properly and secondly (doh) it was returning #N/A as my sample data did not have a returnable value i.e the tran number didn't have an associated PO.


Can I ask one more question then. How do I get this formula to return an error message to the user of "PO needed" or "No valid PO found" rather than "#N/A"?
 
Hi Mel ,


If you are using Excel 2007 and later , it becomes simple ; instead of the earlier formula , use the following :


=IFERROR(INDEX(B2:B10,MATCH(1,((A2:A10=123)*(B2:B10<>"")),0)),"PO Needed")


entered as an array formula , using CTRL SHIFT ENTER.


Instead of the text "PO Needed" , you can use any other useful message.


If you do not have Excel 2007 or later , then you will have to use :


=IF(ISERROR(INDEX(B2:B10,MATCH(1,((A2:A10=123)*(B2:B10<>"")),0))),"PO Needed",INDEX(B2:B10,MATCH(1,((A2:A10=123)*(B2:B10<>"")),0)))


again entered as an array formula , using CTRL SHIFT ENTER.


Narayan
 
Great, thank you. I haven't tested this but from looking at it the formula makes sense. The spreadsheet is being built on 2007 but I am converting it to 2003 compatible mode as it's being sent on elsewhere and I think they may have an earlier version.


Thanks again all!
 
You must log in or register to reply here.
Back
Top