Compare three columns of data to see what is a duplicate and what is not

Hello, I have three different rows of data in an excel spreadsheet:
||All Forms||Forms Used 2011||Forms Used 2012||
||FormA||FormE||FormC||
||FormB||FormA||FormD||
||FormC||FormC||FormG||
||FormD||FormZ||FormY||

The "All Forms" column has all the different types of forms that exist, whereas the other two columns have a list of forms used in 2011 and 2012. None of the columns are in order or anything (and the data from column A doesn't correspond with the data in column B, etc)

I want to ideally make it so that the 2011 and 2012 columns 'line up' with the "All Forms" column (basically making it so that if a form was not used in 2011 or 2012, that cell would show up blank in the 2011 or 2012 row that corresponds to the "All Forms" row).

Basically, I'm trying to get something like this:
||All Forms||Forms Used 2011||Forms Used 2012||
||FormA||FormA||(blank)||
||FormB||FormB||FormB||
||FormC||(blank)||(blank)||
||FormD||FormD||(blank)||

Above, row 2 would indicate that FormA wasn't used in 2012.
Row 3 would indicate that FormB was used in both 2011 and 2012.
Row 4 would indicate that FormC wasn't used in 2011 or 2012.
Row 5 would indicate that FormD wasn't used in 2012.


Is there any way to do this?

Try this:-

Please Login or Register  to view this content.

Regards Mick

Originally Posted by Richard Buttrey

Hi,

Your explanation does not seem consistent with your (albeit small) 4 result rows.

i.e. Form B (row 3) only exists in 2011
Form C (row 4) does exist in 2011
Form D (row 5) was used in 2012

Can you clarify

My apologies, I meant to put "etc." at the bottom. In the spreadsheet, there are thousands of rows. Anything in the 2011 and 2012 columns will be in the "All Forms" column.

Originally Posted by MickG

Try this:-

Please Login or Register  to view this content.

Regards Mick

I get a Runtime Error 424, Object required. It highlights the following line:
If Ray(R, 2) <> "" Then .Item(Ray(R, 2)).Offset(, 2) = Ray(R, 2)

If you want to do this without any code, use a vlookup funtion. Attached is an example I did using your template, it should be pretty self explanatory but let me know if you have questions.
vlookup example.xlsx

Originally Posted by brandonlaw

If you want to do this without any code, use a vlookup funtion. Attached is an example I did using your template, it should be pretty self explanatory but let me know if you have questions.
Attachment 161155

The vlookup function actually worked perfectly! Wasn't aware of its functionality, thanks!

Originally Posted by instinctellekt

The vlookup function actually worked perfectly! Wasn't aware of its functionality, thanks!

No problem. It's a great funtion to know, saves TONS of time. I'm not great with excel, but Vlookup saves me hours of work constantly.