Pull vlookup information based on a set of conditions into a table/list

Psygrrl88

New Member
Joined
Dec 6, 2021
Messages
14
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

ID NumberLast NameState of ResidenceCounty of ResidenceCountry of Residence
123456AndrosMOColeUS
234567BakerMO (ERROR (BLANK))US
345678CarnegyIA (ERROR (State not MO))MillerUS
456789DavidsonARUS
567890ElstromMOBrazil 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 NumberColumn ReferenceError
(filled from a vlookup from previous column)
Description
234567County of Residence (ERROR (BLANK))State is Missouri and county is blank<-------this box is filled from a vlookup
345678State of Residence (ERROR (State County Mismatch))State is not Missouri and County is not blank
567890Country of Residence ERROR(Country State Mismatch)Country is not US and State is not Blank
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Ok - new plan - I am just going to make a very long list of macros to perform the edit checks and copy and paste the data using the macros. Seems to be easier than automating the process.
Is there a way to have a macro check for two different filter outcomes? For example;
If column A is blank and Column B is not blank (greater than 0)
-OR-
if column B is blank and Column A is not blank (greater than 0)

I can check for one of these, but I would like to be able to write just the one macro, instead of having to split it up into two if possible.
 
Upvote 0

Forum statistics

Threads
1,223,248
Messages
6,171,027
Members
452,374
Latest member
keccles

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top