I have two combining files that can be controlled by the field "YR_Q". The top 70% of records are "YR_Q" 11.3. The balance of the records are "YR_Q" 4.3. Iuse the following code to eliminate duplications between the two record types, based on YR_Q", 11.3 & 4.3.
=IF(COUNTIF(B:B,B6)>1,"X","")
Dad worked good sense. The two record sets had no all duplicate records. When looking at each set separately. The duplications occurred between the two sets and were indicated with the "X".
Now my requirement has changed a bit. the records that contain "11.3". Have many duplications within that record set. I have no need to indicate the duplications that occur in this set. My condition is this: I need to mark, the lower set (4.3) for every record that has a duplicate record amongst the "11.3" records. The column B:B is the address field that contains the information that will contain the information to determine the duplication.
When I mentioned record sets. It is to tables imported into the same spreadsheet with two important fields.
B:B ADDR - address to determine duplication
X:X YR-Q - to determine record sets
I hope I've explained myself better than I have in the past.Thanks for taking the time to read this.
Bob
SideBar: some people have mentioned the fact that Excel might choke on the above calculation based on 200,000 records. I just wanted to pass on my experience with length of time it took. Approximately 4 hours.
But same formula used with 1,0109,000 started running yesterday at 4:00pm , and today at 6:35pm is that 69%. Time is extreme but not at all when I think of the alternative checking a sorted database three or four records at a time.
=IF(COUNTIF(B:B,B6)>1,"X","")
Dad worked good sense. The two record sets had no all duplicate records. When looking at each set separately. The duplications occurred between the two sets and were indicated with the "X".
Now my requirement has changed a bit. the records that contain "11.3". Have many duplications within that record set. I have no need to indicate the duplications that occur in this set. My condition is this: I need to mark, the lower set (4.3) for every record that has a duplicate record amongst the "11.3" records. The column B:B is the address field that contains the information that will contain the information to determine the duplication.
When I mentioned record sets. It is to tables imported into the same spreadsheet with two important fields.
B:B ADDR - address to determine duplication
X:X YR-Q - to determine record sets
I hope I've explained myself better than I have in the past.Thanks for taking the time to read this.
Bob
SideBar: some people have mentioned the fact that Excel might choke on the above calculation based on 200,000 records. I just wanted to pass on my experience with length of time it took. Approximately 4 hours.
But same formula used with 1,0109,000 started running yesterday at 4:00pm , and today at 6:35pm is that 69%. Time is extreme but not at all when I think of the alternative checking a sorted database three or four records at a time.
Last edited:
