Good afternoon excel friends!
I am using excel 2007 on windows 7 pro. I will try and explain my situation generically first, but then give a detailed scenario in case it is to confusing.
I am stumped by this one despite several hours of playing around with it, although i admit i am not an expert. Basically i have a long list of data (8 to 16 digit alphanumeric from barcode scans, hence forth known as sample ID's) in 4 different columns, with a lot of duplicates. I have an additional 9 different columns that need to replicate the Sample ID's by determining which columns contain the duplicates, however within those 9 different columns the same Sample ID can only occur once. I played with this code:
=IF(ISERROR(MATCH(D2,$F$2:$F$6,0)),"",D2)
However, using this code it finds the duplicates only for two columns. In response i just made several columns that searched the many combinations of the 4 columns together. This worked, but if a sample ID appeared in more than 2 columns that sample ID appeared many times on the new columns, and the ID can only appear once total. I used google to find this code, but it isnt working and it is beyond my talent level. Looking for advice.
=IFERROR(INDEX($F$2:$F$10,SMALL(IF(ISNA(MATCH($F$2:$F$10,$K$2:$K$4,0)),ROW($F$2:$F$10)-ROW($F$2)+1),ROW(2:2))),"")
OK, if that was confusing or not clear, let me explain the entire process in case there is a better way to do it that i have not imagined.
We get a box of samples all the time, each with a unique Sample ID barcode. In each box we have 4 sheets of paper that tell us which Sample ID's need what kind of testing. VMT, RPR, ABO, and AbScr are the 4 tests that might be requested. Some Sample ID's only need one, and some Sample ID's need 2-3 of the testing. This means that we have a lot of sample ID's that show up on both sheets, thus the duplicates. What we need is our technicians to be able to select a VMT column in excel and begin scanning all of the sample ID's on the VMT page, then an RPR column for the RPR IDs, etc.
Once they are done scanning, we need a formula that automatically creates a list of which sample ID's need which testing group. The testing group being defined as the total amount of tests a Sample ID needed. The possible combinations are VMT/RPR/AbScr testing, VMT/RPR, VMT/ABO, VMT/AbScr, RPR/ABO, only VMT, only RPR, only ABO, or only AbScr. Each one of those testing groups needs to have its own column, and the Sample ID's cannot appear in more than one column since when we import this spreadsheet into our testing program it will throw an error.The issue i have ran into is the samples that require VMT/RPR/AbScr testing show up on the VMT/RPR and VMT/AbScr columns when i use the first forumla i posted above, which causes and issue.
Any idea on what formula i could use? Here is a copy of the spreadsheet if you would like a visual aid: http://willthomason.com/share/autoaccessioning.xlsx
I am using excel 2007 on windows 7 pro. I will try and explain my situation generically first, but then give a detailed scenario in case it is to confusing.
I am stumped by this one despite several hours of playing around with it, although i admit i am not an expert. Basically i have a long list of data (8 to 16 digit alphanumeric from barcode scans, hence forth known as sample ID's) in 4 different columns, with a lot of duplicates. I have an additional 9 different columns that need to replicate the Sample ID's by determining which columns contain the duplicates, however within those 9 different columns the same Sample ID can only occur once. I played with this code:
=IF(ISERROR(MATCH(D2,$F$2:$F$6,0)),"",D2)
However, using this code it finds the duplicates only for two columns. In response i just made several columns that searched the many combinations of the 4 columns together. This worked, but if a sample ID appeared in more than 2 columns that sample ID appeared many times on the new columns, and the ID can only appear once total. I used google to find this code, but it isnt working and it is beyond my talent level. Looking for advice.
=IFERROR(INDEX($F$2:$F$10,SMALL(IF(ISNA(MATCH($F$2:$F$10,$K$2:$K$4,0)),ROW($F$2:$F$10)-ROW($F$2)+1),ROW(2:2))),"")
OK, if that was confusing or not clear, let me explain the entire process in case there is a better way to do it that i have not imagined.
We get a box of samples all the time, each with a unique Sample ID barcode. In each box we have 4 sheets of paper that tell us which Sample ID's need what kind of testing. VMT, RPR, ABO, and AbScr are the 4 tests that might be requested. Some Sample ID's only need one, and some Sample ID's need 2-3 of the testing. This means that we have a lot of sample ID's that show up on both sheets, thus the duplicates. What we need is our technicians to be able to select a VMT column in excel and begin scanning all of the sample ID's on the VMT page, then an RPR column for the RPR IDs, etc.
Once they are done scanning, we need a formula that automatically creates a list of which sample ID's need which testing group. The testing group being defined as the total amount of tests a Sample ID needed. The possible combinations are VMT/RPR/AbScr testing, VMT/RPR, VMT/ABO, VMT/AbScr, RPR/ABO, only VMT, only RPR, only ABO, or only AbScr. Each one of those testing groups needs to have its own column, and the Sample ID's cannot appear in more than one column since when we import this spreadsheet into our testing program it will throw an error.The issue i have ran into is the samples that require VMT/RPR/AbScr testing show up on the VMT/RPR and VMT/AbScr columns when i use the first forumla i posted above, which causes and issue.
Any idea on what formula i could use? Here is a copy of the spreadsheet if you would like a visual aid: http://willthomason.com/share/autoaccessioning.xlsx
