if/then statements

M

Molly

There are two spreadsheets in this project. On one spreadsheet I have over
8000+ rows with several columns: one column contains a product number,
another contains colors available for the product, and several other columns
describing the product. On the second spreadsheet I have about 2000 rows with
the same information.

Problem is: I need to somehow ask Excel to look at the second spreadsheet,
taking each row at a time, move over to the first spreadsheet, check to see
if the product number exists and then check to see if the color in the second
spreadsheet exists in the first spreadsheet. If the product from the second
spreadsheet doesn't exist on the first, I need to tell Excel to add a row to
the first spreadsheet. If the product from the second spreadsheet does exist
on the first and the color does not exist, I need to tell Excel to add a
return in the color column and then add the color from the second spreadsheet
to the first. If the product from the second spreadsheet does exist on the
first and the color does exist, I need to tell Excel to copy the row to
another blank spreadsheet.

HELP!!
 
D

Dnereb

Do you have to keep your stuf in Excel? Or is transferring it to Access
an option?
This volume of data and comparing "records"to search if they already
exist and the looking up based on productnumber or all products that
have a yellow variaty can be done far better in Access.
[email protected]
 
M

Molly

Thanks for your response.

I was this -->| |<-- close to importing the spreadsheets into Access and
doing just that. I can do this in any format to get it done -- just don't
want to spend all weekend figuring it out as I need it Monday.

I'm in no way, shape or form an expert in Access so you might see further
postings in the Access discussion groups!
 
B

Blue Hornet

A relatively quick and simple way to do this is:
1. Create a new column IN EACH SHEET that concatenates the unique
information you want to capture in Sheet2 (the one with the 2000 or so
lines). For example if your columns are like:
A = Ford
B = Pickup
C = Blue
D = 4x4
then E1 on BOTH Sheet1 and Sheet2 could be:
= A1 & "-" & B1 & "-" & C1 & "-" D1 or:
Ford-Pickup-Blue-4x4

Copy this formula down all 8000+ rows.

2. Now do a VLOOKUP from Sheet1 into Sheet2 like:
= if( isna( VLOOKUP( E1, Sheet2!$E$1:$E$8000, 1, false)), "Copy this
row!", "")

Copy this formula down all 8000+ rows.

3. You'll have a list of "Copy this row!" alongside each of the rows
that need to be copied into Sheet2. You can sort on this value, then
copy and paste the entire group into Sheet2.

4. Finally, you can either delete or hide the concatenation and
VLOOKUP colums (I like to keep them as a check that I'm not missing any
values).

Chris
 
Top