Refrence data in a row

Hello everyone, thanks in advance for any help. I am comfortable with excel but can't find anyway to accomplish what I need done. I have a report that I have to do at work each day that takes hours. It only takes so long because I have to repeat my own work. I need a formula that will search for the last instance of a string on the report and then gather the information I input. For example. In cell A1 there would be a order number. In cell A3 I would input some information. Sometime later the same order number would show up in cell A1, I want the data in cell A3 to auto-populate. There can be multiple instances of the order number. It would be best if it can reference the last instance for the information to pull from. I hope that makes sense. Thank you

Originally Posted by Derek210

... I hope that makes sense ...

It doesn't make much sense to me. Perhaps you can attach a sample workbook (the FAQ describes how to), and in that you can explain exactly what you want to achieve, perhaps with a Before and After sheet which show what data you are acting upon (maybe compile the results manually).

Pete

I had to change all the confidential information but here is an example. The full sheet is at about 60,000 lines and growing by 500+ per day. In my example I want the information that has already been put in for customer A, in cells I through M, to be copied down. I want to use the order number for the reference since the customers will sometimes have multiple orders.

You didn't put an After sheet in, so I'm still not clear what you want. You have data in columns I to M for customer A with order number 1234 on row 2, and also on row 6, but not on row 10. So, what do you want to happen with this data?

Pete

Sorry I didn't post an after. It would be the exact same sheet with the data from row 6 columns I through M copied down to row 10 columns I through M. I want a formula that looks for the latest instance of the order number and then copies the data from I through M to the newer instance of the order number.

I put the formula in cells on row 8 in the attached file, and then copied them down to the bottom of your table - note that they do not automatically copy down, as is usual with a table, because you have some manual data entries above. All the formulae are array formulae, which means that you have to commit them using the key combination of Ctrl-Shift-Enter (CSE) instead of the usual |Enter|. When you look at the formulae in the formula bar you will see curly braces { } around the formula, but you must not type these yourself. If you need to edit the formula, then you must use CSE again to commit them. Here are the formulae in the cells stated:

I8: =IF(IFERROR(MAX(IF(($B$1:$B7=$B8)*($C$1:$C7=$C8),ROW($1:7),-1)),-1)>0,A8,"")

J8: =IF(IFERROR(MAX(IF(($B$1:$B7=$B8)*($C$1:$C7=$C8),ROW($1:7),-1)),-1)>0,INDEX(J$1:J7,MAX(IF(($B$1:$B7=$B8)*($C$1:$C7=$C8),ROW($1:7),-1))),"")

K8: =IF(IFERROR(MAX(IF(($B$1:$B7=$B8)*($C$1:$C7=$C8),ROW($1:7),-1)),-1)>0,INDEX(K$1:K7,MAX(IF(($B$1:$B7=$B8)*($C$1:$C7=$C8),ROW($1:7),-1))),"")

L8: =IF(IFERROR(MAX(IF(($B$1:$B7=$B8)*($C$1:$C7=$C8),ROW($1:7),-1)),-1)>0,INDEX(L$1:L7,MAX(IF(($B$1:$B7=$B8)*($C$1:$C7=$C8),ROW($1:7),-1))),"")

M8: =IF(IFERROR(MAX(IF(($B$1:$B7=$B8)*($C$1:$C7=$C8),ROW($1:7),-1)),-1)>0,INDEX(M$1:M7,MAX(IF(($B$1:$B7=$B8)*($C$1:$C7=$C8),ROW($1:7),-1))),"")

Note that the formula in J8 can be copied across into K8:M8, but you will need to apply a date format to M8, and then the row of formulae can be copied down.

Notice also that the formulae pick up the repeat of Customer B in row 9, although the empty cells from the earlier record show up as zeros.

Hope this helps.

Pete

Thank you so much Pete! That is exactly what I was looking for! This helps a TON

Well, Derek, I'm glad to help, though I'm not really sure how you would use this, but thanks for the rep anyway.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Pete

It's tough to explain how this helps. Basically new orders get added to the report that need to be checked that day. Most of the time they have been checked recently, because they have to be checked every week until completed. So the formulae that you made saves me 5 minutes per order in gathering the same information again. Now a 10 minute task becomes a 5 minute task, and that saves me about an hour a day.