Create a list with most recent data available
Question: i have two sets of data - one has an identifier column and one result column.
A2 data1 B2 22
A3 data2 B3 55
A4 data3 B4 44
A5 data5 B5 22
A6 data4 B6 33
A7 data9 B7 22
A8 data6 B8 44
A9 data88 B9 55
in the second set
D2 data1 E2 33
D3 data2 E3 55
D4 data3 E4 44
D5 data4 E5 33
D6 data88 E6 12
the new list should change B2 from 22 to 33.
If there is no change it shows the first sets result . if there is a change it reports from the second set.
To complicate it... the first set of data is twice as long as the second one. not all data is in the second set and finally the lists are not sorted and cant be.
Formula in F3:
copied down as far as needed.
Explaining formula in cell B13
Step 1 - Count given value in cell range
The COUNTIF function counts cells based on a condition, it will return 1 if the value exists in cell range $B$13:$B$17.
COUNTIF($B$13:$B$17, E3)=0
becomes
COUNTIF({"data1";"data2";"data3";"data4";"data88"},"data1")=0
becomes
1=0 and returns FALSE.
Step 2 - Which cell range?
The IF function determines from which cell range the formula retrieves the value needed.
IF(COUNTIF($B$13:$B$17, E3)=0, INDEX($C$3:$C$10, MATCH(E3, $B$3:$B$10, 0)), INDEX($C$13:$C$17, MATCH(E3, $B$13:$B$17, 0)))
becomes
IF(FALSE, INDEX($C$3:$C$10, MATCH(E3, $B$3:$B$10, 0)), INDEX($C$13:$C$17, MATCH(E3, $B$13:$B$17, 0)))
and returns
INDEX($C$13:$C$17, MATCH(E3, $B$13:$B$17, 0))
Step 3 - Get value
The MATCH function returns the relative position of a value in a cell range or array.
INDEX($C$13:$C$17, MATCH(E3, $B$13:$B$17, 0))
becomes
INDEX($C$13:$C$17, 1)
The INDEX function returns a value based on a row number (and a column number if needed).
INDEX($C$13:$C$17, 1)
returns 33 in cell F3.
Get Excel *.xlsx file
Compare category
This article demonstrates ways to extract shared values in different cell ranges, two and three cell ranges. The Excel 365 […]
Array formula in B15: =INDEX($B$3:$B$12, MATCH(0, COUNTIF($B$14:B14, $B$3:$B$12)+IF(((COUNTIF($D$3:$D$11, $B$3:$B$12)>0)+(COUNTIF($F$3:$F$12, $B$3:$B$12)>0))=2, 0, 1), 0)) Copy cell B15 and paste it to […]
This article shows how to compare two nonadjacent cell ranges and extract values that exist only in one of the […]
How would you figure out an unique list where the sum of in one column doesn't match the other column? […]
This article demonstrates formulas that extract differences between two given lists. The first formula in cell B11 extracts values from […]
I will in this blog post demonstrate a formula that extracts common records (shared records) from two data sets in […]
This article explains how to extract values that exist in three different columns, they must occur in each of the […]
Overview Updating a list using copy/paste is a boring task. This blog article describes how to update values in a price […]
Today I am going to show you how to quickly compare two tables using Conditional Formatting (CF). I am going […]
This article demonstrates formulas that extract values that exist only in one column out of two columns. There are text […]
Question: How do i remove common values between two lists? Answer: The solution in this article, removes common values and […]
The image above shows an array formula in cell B12 that extracts values shared by cell range B2:D4 (One) and […]
The formulas above extracts values that exists only in one or the other cell range, if you are looking for […]
In this example we are going to use two lists with identical columns, shown in the image above. It is […]
This article describes an array formula that compares values from two different columns in two worksheets twice and returns a […]
Functions in this article
More than 1300 Excel formulas
Excel formula categories
Excel categories
One Response to “Create a list with most recent data available”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
"A=10
B=8" "Total:
A=?
B=?"
"A=10
B=9"
"A=10
B=10"
"A=10
B=11"
"A=10
B=12"
"A=10
B=13"
"A=10
B=14"
"A=10
B=15"
"A=10
B=16"
"A=10
B=17
here column1 is the values for A and B contained in one cell. I want to sum this values from top to bottom of column for A and B separately, How can i do this?