Column matching the new rows

blueskidd

New Member
Joined
Aug 28, 2014
Messages
5
I have index,match functions on most of my cells to pull in data that matches multiple criteria from other worksheets. I'd like to now match across the rows and columns in this sheet and if there is not a match based on three columns matching three column criteria that the non-matching column data be put on a new row. Can this be done or is there a simpler way to do this?
Example from:

Name Resources VNum ProjV Role ProjRole
Apple, Jane Apple, Jane 115055 115055 Projmgr Projmgr
Apple, Jane Aster, Bob 112034 114077 BusAnalyst BusAnalyst
Aster, Bob Atter, Sam 114077 112034 BusAnalyst Projmgr


To:


Name Resources VNum ProjV Role ProjRole
Apple, Jane Apple, Jane 115055 115055 Projmgr Projmgr
Apple, Jane 114077 BusAnalyst
Aster, Bob 112034 BusAnalyst
Aster, Bob 114077 BusAnalyst
Atter, Sam 112034 Projmgr
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
If not, can I create a macro and bring the info into a new sheet sorted so that I can easily see missing data with side by side comparison of columns but matching rows together and non-matching on separate line? Thanks!
 
Upvote 0
blueskidd,

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


If not, can I create a macro and bring the info into a new sheet sorted so that I can easily see missing data with side by side comparison of columns but matching rows together and non-matching on separate line? Thanks!


It is difficult to determine what cells, rows, columns your text data is in. And, it is difficult to determine what cells, rows, columns your results are in.


So that we can get it right the first time:


Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
1. MrExcel HTMLMaker20101230
https://onedrive.live.com/?cid=8cffdec0ce27e813&sc=documents&id=8CFFDEC0CE27E813!189

Installation instructions here:
http://www.mrexcel.com/forum/board-announcements/515787-forum-posting-guidelines.html#post2545970

2. Excel Jeanie
Download


If you are not able to give us screenshots:
You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.
 
Upvote 0
Thanks! I created a dummy workbook on a Mac with Office 11 but I normally use Win 7 with Office 2011 but its locked down and can't send even a dummy workbook. The data sheet is basically the data I pull in from three other sheets for comparison without the index,match functions. The Compare sheet, I stopped after line 10 but it should provide you what I'm looking for. I need to compare the Name, Proj VM, Project Name, and Project Role to CA Role to Resource, VM, Investment, CA Role. If they match they can stay on same line and the hours for both should match. If any of those fields don't match then they get a new row and the missing fields are highlighted and there is a red Varience as hours will not match. Does that make sense? Here is the link to the box.net file Compare.xls https://app.box.com/s/tdgb63uze88a4kphrq0q
 
Upvote 0
blueskidd,

Thanks for the workbook.

I can not see the logic for returning the results that you are looking for.


Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

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