I'm trying to consolidate/compare a list of numbers from a daily report. The report will have pairs of numbers each day that correspond, like this:
30000 9959
30001 9957
30002 9958
31010 4752
etc
The left numbers are not always sequential, and the left column can change from day to day. I need to take a 5 day range of these reports and see how many of the left numbers had the associated right number change. I can use VLOOKUP() inside IFERROR() to grab the corresponding number from each day's report, or return 0 if the left number doesn't appear.
What I can't do is figure out how to make a list of all the left numbers that have appeared in the given 5 report columns. For example:
Day1
1 10
2 11
3 12
4 13
5 14
Day2
1 10
3 11
4 14
5 14
6 14
Day3
1 10
2 11
4 14
5 14
6 14
8 15
So I would need to generate a list of numbers that was:
1
2
3
4
5
6
8
Assuming a make a column with 123456789 in A, and then have each days' data in B, C, and D:
The only way I have been able to think of would be with an IF() of
=IF(COUNTIF(B:B,A1)+COUNTIF(C:C,A1)+COUNTIF(D:D,A1),"x","")
And then filter the column to only show the "x" cells. That works fine for the above, but there's 14000 numbers, ranging from 30000 to 50000. Is there any other way to generate this column of numbers besides what I'm doing? The numbers can have gaps of 1-2k in them frequently, so I'm looking at filtering a giant number of cells to do that.
Thanks in advance for any advice, even if it's just telling me to set up the filter for 30000 to 50000!
EDIT - I ended up doing the above this morning, and it worked, but I'm kind of brute-forcing this and hoping there's a better way.
Bookmarks