So - Im not sure if my 2016 version of excel can even perform the function I want to do. But basically - I have a list of "possible errors" for a spreadsheet that has 40+ columns and over 2k rows. I need to be able to filter for the errors (I.e. if Column AU= 0 and Column AV not = 0 OR if column AV = 0 and Column AU not = O) then I need it to pull the information from columns A,B,C from that row and put it into a table. I have a list of somewhere like 120 possible errors.
Example: (Column Information)
A = ID Number
B = Last name
C = State of Residence
D = County of Residence
E = Country of Residence
IF(C=MO and D=Blank) THEN(error)
IF(D=not Blank and C=not MO) THEN(error)
IF(E=not US and C= not blank) THEN(error)
and so on. Theres a lot of these.
I don't need the errors to show up in the spreadsheet, just showing you where they are
But then I need to pull;
ID Number - Column Header for Error(in this case Residency Columns C,D,E) - vlookup for description of error from pre-made table.
Example: (Column Information)
A = ID Number
B = Last name
C = State of Residence
D = County of Residence
E = Country of Residence
ID Number | Last Name | State of Residence | County of Residence | Country of Residence |
123456 | Andros | MO | Cole | US |
234567 | Baker | MO | (ERROR (BLANK)) | US |
345678 | Carnegy | IA (ERROR (State not MO)) | Miller | US |
456789 | Davidson | AR | US | |
567890 | Elstrom | MO | Brazil ERROR(Country not US) |
IF(C=MO and D=Blank) THEN(error)
IF(D=not Blank and C=not MO) THEN(error)
IF(E=not US and C= not blank) THEN(error)
and so on. Theres a lot of these.
I don't need the errors to show up in the spreadsheet, just showing you where they are
But then I need to pull;
ID Number - Column Header for Error(in this case Residency Columns C,D,E) - vlookup for description of error from pre-made table.
Student ID Number | Column Reference | Error (filled from a vlookup from previous column) | Description | |
234567 | County of Residence | (ERROR (BLANK)) | State is Missouri and county is blank | <-------this box is filled from a vlookup |
345678 | State of Residence | (ERROR (State County Mismatch)) | State is not Missouri and County is not blank | |
567890 | Country of Residence | ERROR(Country State Mismatch) | Country is not US and State is not Blank | |