Grouping According to Sets of Rows
Ok, that is easiest. VLOOKUP() will work just great.
Set up a formula similar to this in a column next to the entries on the Base
Report (assuming at row 10 and "User#" entries are in column A, and that the
entries from the consolidated report start in column a and go down to row 8)
=VLOOKUP(A10,$A$1:$B$8,2)
and just drag it down the page as far as you need to go. Presumes data is
laid out like you showd on the Consolidated Report for the 1st 2 columns also.
To rapidly identify duplicates in the Base Report, if they are sorted
ascending using both column A and column B, then you can put this formula in
an empty cell on the SECOND row of that group:
=IF(AND(A11=A10,B11=B10),"DUPLICATE","")
That will identify cases where both column A and B in a row are duplicates
of contents of A and B in the row above them.
I'll work up some quick code to delete duplicate rows that can be run from a
macro for you and post again later.
"Balthanon" wrote:
#2 is fine for my purposes--in fact that's actually what I'm looking
for, since identifier 1 tells me that the user has infoA, infoB, infoC,
and infoD, it's just not stored that way in our systems. I can go
through and delete the duplicated rows manually if need be, though if
that could be done automatically it would be nice as well.
Also, User3 should be identifier1--the identifier is what I am actually
comparing between the reports.
JLatham wrote:
I think this can be done with VLOOKUP(), but need to make sure of what your
end result needs to look like.
Choice 1:
Consolidated Report
User1 identifier1 infoA infoB infoC infoD
User2 identifier2 infoC infoD
User3 identifier1 infoA infoB infoC infoD
(and should identifier for User3 be identifier1 or identifier3?)
Choice 2:
Base Report:
User1 Identifier1
User1 Identifier1
User1 Identifier1
User1 Identifier1
User2 Identifier2
User2 Identifier2
User3 Identifier3
User3 Identifier3
User3 Identifier3
User3 Identifier3
Which is the result you want. #2 is easier than #1 because of the varying
number of data items for each user in the Base Report.
|