What "IF" formula/Macro should I use to automatically choose which among the sources releases most recent data

[platoniciorf]

In a worksheet, I have three rows of data from three different sources. I need to get the data from whichever of three sources releases the most recent data.Example: Let's name the sources, Source 1, Source 2, and Source 3 in one column. In another column is "Last Update" which refers to the time when the data was last updated. Case 1: If Source 1 releases data on 07/14/15, Source 2 releases data on 1:41 pm today, and Source 3 releases data on 07/14/15. In that case, the data should be coming from Source 2 since it shows in the "Last Update" column that the data was last updated just today, showing time today.Case 2: If all the three sources release date on the exact same time/date, the data should always be from Source 1Is there a way to automatically do this using "IF" formula or a macro?

 

[Giordano Bruno]

Assuming that the dates are entered as dates and not text, why not simply put a formula at the top of each column giving the maximum of the range below and then use this to determine which value to use.

 

[platoniciorf]

Please note the dates are not entered manually and so as the data. These are automatically generated from a software to Excel.

 

[Giordano Bruno]

Sa are they dates, i.e. day numbers, or just text?

 

[platoniciorf]

Date numbers (e.g., 7/22/2015, 9:21:50 AM, 4:00:00 AM). That means sources release data at those times respectively. In that case in the said example, the source that released data at 9:21:50 AM should be chosen as it releases data just today at the most recent time. Please help me on this. Thanks!

 

[Giordano Bruno]

I think I don't have a clear picture of what you are trying to achieve. I assume that the last update is the value of the MAX() of the range of dates? Therefore there will always be a date that is equal to the "Latest Update" How is the data arranged on the worksheet. You mention three rows of data and the following is one way of extracting data from the correct row.

Suppose the Last Update cell reference is A15 and that the Source dates are in cells B15, B16 and B17. Put the following formula in cell A19 =IF(B15=A15,ROW(B15),IF(RB16=A15,ROW(B16),ROW(B17))). This is used to identify the relevant row for the data.

In a cell in column B on any new row, insert the following formula =INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&$A$19)

Copy this formula to the right up to the last data column. It will extract data from the rows above depending upon the row selected by the formula in cell A19

 

[platoniciorf]

Sorry if I'm being so unclear with my instructions. Let me give you an example:I have 4 columns in my worksheet. Column A for the reference no., Column B for the data sources, Column C for the date/time of last update, and Column D for the data to be extracted. In column A, cells A2, A3, A4 are the ref nos.. For this purpose, let's just name it Ref 1, Ref 2, and Ref 3. In column B, cells B2, B3, B4 are the sources namely Source 1, Source 2, Source 3. And in column C, cells C1, C2, C3 are time/date when each source last released data (Please note that all dates/time and all data are automatically generated upon accessing the file). For this example, upon generating data, let's say the three sources releases data on 7/23/2015 4:59:50 AM, 7/23/2015 1:52:01 PM, 7/23/2015 4:00:00 AM respectively, these would be in cells C2, C3, C4.Hence, since in the example, Source 2 releases data on 07/23/2015 1:52:01 PM, the most recent compared to the other two, in another column D, the formula I'm trying to formulate should then get/choose Ref 2 in Cell A2.Hope this clear things out. Please help me still on this. I'm nearing to finishing my project. This would be the last one I needed. I would very much appreciate your help. Thanks!

 

[Giordano Bruno]

Does this formula work?
=INDEX(A2:A4,MATCH(MAX(C2:C4),C2:C4,0))

 

[platoniciorf]

I tried it but it didn't work.

 

[platoniciorf]

I just did something with the dates which were generated and I tried your formula again and it worked! )))) Thank you so much! ))))))http://www.mrexcel.com/forum/images/smilies/icon_smile.gif