Comparing data in two pairs of columns, and automatically deleting based on relative value

**Crashed** · 01-23-2016, 03:56 PM

I have two columns, the first contains the name of a chemical, the second contains a numerical concentration for that chemical.

I need to compare these two columns to a second pair of columns containing the same two variables, and delete any compounds in the first pair which are present in the second pair, unless the concentrations of the first pair are 20 times greater than that of the second pair.

Is there a way to do this using COUNTIF or something similar? I would be happy to provide more information if needed, and grateful for any advice.

**mrice** · 01-23-2016, 04:02 PM

Probably COUNTIFS if you are dealing with more than one column. An example worksheet would be useful.

**Crashed** · 01-23-2016, 04:14 PM

Thank you for the quick reply, I have attached an example worksheet.

**mrice** · 01-23-2016, 04:54 PM

Decided to use a user defined function.

Please Login or Register  to view this content.

Open the VBA editor by hitting Alt F11.
Insert a new module with Insert - Module
Paste in the above function
Go back to the sheet by hitting Alt F11.

In a cell C3, enter =Compare($E$3:$E$5,A3,$F$3:$F$5,B3)

Remember to save the workbook as a macro enabled workbook .xlsm

**Crashed** · 01-23-2016, 05:08 PM

Hi mrice,

I cannot thank you enough for taking the time to help me with this, it has completely solved my problem! Would this work if the columns changed their titles, or if more chemicals were added to pair 2? If not, which parts of the code would I need to alter?

**mrice** · 01-23-2016, 05:34 PM

The column names aren't read so you can call them what you wish.

**Crashed** · 01-23-2016, 05:36 PM

Amazing, thank you so much!

**Crashed** · 01-25-2016, 03:57 PM

Sorry to be a major pain, but I have a #Value error when using the code you provided and I cannot figure out why it is giving it. It seems to work with everything else apart from a particular compound name, as soon as I change the compound name in the second pair it works again. I have attached the worksheet and highlighted the relevant cells in red. Any help would be greatly appreciated.

**mrice** · 01-25-2016, 04:22 PM

This slight modification explicitly specifying the match type seems to help.

Please Login or Register  to view this content.

**Crashed** · 01-25-2016, 04:26 PM

Thank you so much for this, and all the other help you've given!

Comparing data in two pairs of columns, and automatically deleting based on relative value

LinkBack

Thread Tools

Rate This Thread

Display

Comparing data in two pairs of columns, and automatically deleting based on relative value

Re: Comparing data in two pairs of columns, and automatically deleting based on relative v

Re: Comparing data in two pairs of columns, and automatically deleting based on relative v

Re: Comparing data in two pairs of columns, and automatically deleting based on relative v

Re: Comparing data in two pairs of columns, and automatically deleting based on relative v

Re: Comparing data in two pairs of columns, and automatically deleting based on relative v

Re: Comparing data in two pairs of columns, and automatically deleting based on relative v

Re: Comparing data in two pairs of columns, and automatically deleting based on relative v

Re: Comparing data in two pairs of columns, and automatically deleting based on relative v

Thread Information

Users Browsing this Thread

Similar Threads

Comparing two pairs of columns and copying corresponding values into an a different column

[SOLVED] Comparing data in two different columns and deleting entire row

Comparing columns and deleting those that do not match

Deleting rows based on a cells value that is relative to the cell above it

Comparing & deleting data in 2 columns

Comparing Columns and Deleting values

need help comparing and deleting columns in different xls files

Bookmarks

Bookmarks

Posting Permissions