baleman2
asked on
Excel 2007 Comparing Cell Contents with IF Statements
I have data in columns for which I need to make 3 different comparisons.
I. Exact data comparison in a specific cell of Column C with all cell contents of Column G.
A. If a "match" is found, I need to move cell contents from 2 different rows to another sheet: Sheet 2
1) Cell C2 contains the data: 1S394A0
a) I need to compare this "exact" data with all the cell contents in Column G
b) An "exact" match is found in cell G22: 1S394A0
2) Move A2, B2, C2, D2, F22, G22, H22 to the same row on Sheet 2
B. For clarification, I have manually made these entries on Sheet 2
The following request may have to do with truncating leading digits/letters
II. Inexact data comparison in a specific cell of Column C with all cell contents in Column G.
A. If a "match" is found of "inexact" data, I need to move cell contents from 2 different rows to another sheet: Sheet 2
1) Cell C9 contains the data: S0001
a) I need to compare the contents of this cell with all the cell contents in Column G
b) An "inexact" match is found in cell G1: 1S0001
c) The leading digit "1" is all that keeps this from being an "exact" match.
d) As long as the 5 digit data in C9 matches any part of the 5 digit data in the cells of Column G,
I would consider that a "match"
2) Move A9, B9, C9, D9, F1, G1, H1 to the same row on Sheet 2
B. For clarification, I have manually made these entries on Sheet 2
III. Data comparison in a specific cell of Column C with all cell contents in Column G results in "no match"
A. If "no match" is found of data, I need to move cell contents from 1 row to another sheet 2
1) Cell C8 contains the data: NRL
a) I need to compare the contents of this cell with all the cell contents in Column G
b) There is no exact ( I ) match and there is no inexact ( II ) match
2) Move A8, B8, C8, D8 to the same row on sheet 2
a) The resulting rows of data containing no matches need to be grouped together, if possible
B. For clarification, I have manually made these entries on Sheet 2
I'm a novice with "IF" statements in Excel, but this is beyond my level of expertise.
I've just inherited this mess from my department head(s) and must clean up tens of thousands of similar entries as presented in this workbook.
Please help.
C--Users-dan-Desktop-Code_Compariso.xlsx
I. Exact data comparison in a specific cell of Column C with all cell contents of Column G.
A. If a "match" is found, I need to move cell contents from 2 different rows to another sheet: Sheet 2
1) Cell C2 contains the data: 1S394A0
a) I need to compare this "exact" data with all the cell contents in Column G
b) An "exact" match is found in cell G22: 1S394A0
2) Move A2, B2, C2, D2, F22, G22, H22 to the same row on Sheet 2
B. For clarification, I have manually made these entries on Sheet 2
The following request may have to do with truncating leading digits/letters
II. Inexact data comparison in a specific cell of Column C with all cell contents in Column G.
A. If a "match" is found of "inexact" data, I need to move cell contents from 2 different rows to another sheet: Sheet 2
1) Cell C9 contains the data: S0001
a) I need to compare the contents of this cell with all the cell contents in Column G
b) An "inexact" match is found in cell G1: 1S0001
c) The leading digit "1" is all that keeps this from being an "exact" match.
d) As long as the 5 digit data in C9 matches any part of the 5 digit data in the cells of Column G,
I would consider that a "match"
2) Move A9, B9, C9, D9, F1, G1, H1 to the same row on Sheet 2
B. For clarification, I have manually made these entries on Sheet 2
III. Data comparison in a specific cell of Column C with all cell contents in Column G results in "no match"
A. If "no match" is found of data, I need to move cell contents from 1 row to another sheet 2
1) Cell C8 contains the data: NRL
a) I need to compare the contents of this cell with all the cell contents in Column G
b) There is no exact ( I ) match and there is no inexact ( II ) match
2) Move A8, B8, C8, D8 to the same row on sheet 2
a) The resulting rows of data containing no matches need to be grouped together, if possible
B. For clarification, I have manually made these entries on Sheet 2
I'm a novice with "IF" statements in Excel, but this is beyond my level of expertise.
I've just inherited this mess from my department head(s) and must clean up tens of thousands of similar entries as presented in this workbook.
Please help.
C--Users-dan-Desktop-Code_Compariso.xlsx
Last Comment
ASKER
Interested ! What's my next step?
Does that spreadsheet that I created look good enough?
If not, give me some feedback.
I am on my way home right now and out the door. But I'll check for your feedback in an hour and post a solution in about two hours.
If not, give me some feedback.
I am on my way home right now and out the door. But I'll check for your feedback in an hour and post a solution in about two hours.
ASKER
It looks as though the results are spot on.
The blank rows shown in Columns F thru I are the ones where there is "no match"? If there is any way to group those rows together, that would be great. The reason for the request is as follows:
1) The data I submitted to you in my spreadsheet represents only 1 (the smallest) of 25 company divisions for which your solution must work.
2) I'm creating a database query that exports to an Excel (Raw Data) spreadsheet for each company division.
3) Some of these divisions' spreadsheets will have 5k to 10k rows.
4) In searching through those spreadsheets for the "no match" entries, they could be easily missed.
However, that's biscuits and gravy. If that's a lot of trouble, I'm happy with just the biscuits.
The blank rows shown in Columns F thru I are the ones where there is "no match"? If there is any way to group those rows together, that would be great. The reason for the request is as follows:
1) The data I submitted to you in my spreadsheet represents only 1 (the smallest) of 25 company divisions for which your solution must work.
2) I'm creating a database query that exports to an Excel (Raw Data) spreadsheet for each company division.
3) Some of these divisions' spreadsheets will have 5k to 10k rows.
4) In searching through those spreadsheets for the "no match" entries, they could be easily missed.
However, that's biscuits and gravy. If that's a lot of trouble, I'm happy with just the biscuits.
ASKER
RBERKE - are there other problems?
sorry, I got tied up at home. but here are the biscuits I promised.
solution.xlsm
solution.xlsm
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
A click on the button within your "solution" spreadsheet presents me with a "Key to Use" popup. I've tried what I thought were logical cell entries, but nothing has worked thus far.
Is there a specific cell for me to use?
Is there a specific cell for me to use?
ASKER
Holy Crap!
I was keying in a cell (C2) instead of clicking on the C2 cell. A click on the C2 cell then pasted into the "Key to Use" rectangular box.
Results were instantaneous!!!! My Gosh!!!
I'll continue to work on all divisions but this shouldn't take me long now. I thought I'd be spending days on this.
I was keying in a cell (C2) instead of clicking on the C2 cell. A click on the C2 cell then pasted into the "Key to Use" rectangular box.
Results were instantaneous!!!! My Gosh!!!
I'll continue to work on all divisions but this shouldn't take me long now. I thought I'd be spending days on this.
glad to hear it is going well. vba is an incredibly useful tool for things like this.
don't forget to close the problem :)
ASKER
I couldn't be more pleased with the results provided by this solution. What I thought would take weeks ended up taking a few days.
RBURKE is the MAN!!!
RBURKE is the MAN!!!
Microsoft Excel
Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.
144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
For instance, I created a "PasteSpecialOnKeys" macro which makes easy work of this (I created the attached "possible answer" in two minutes).
My macro is pretty fancy, but I could give you a 15 line version that might do what you want.
Are you interested?
rberke
Possible-answer.xlsx