Change cell contents based on other contidions

tracktor

Board Regular
Joined
Sep 13, 2009
Messages
62
If the stock numbers are the same in column A, and in one of the corresponding status codes in column B there is an S (in that same series of same stock numbers), then I want a "Y" in column C, otherwise I just want the contents of column B in column C. There can be 1 to 9 stock numbers that are the same with varied status codes. I have this same scenario for "X", "W", and "WC" in columns d, e, and f. My database is about 100,000 records(rows). I would like this done with a macro if possible. Or if it is easier then functions are OK. Here is a sample of the data. The columns are Stock#, Status Codes, "S", "X", "W", and "WC".

Stock # Status Codes "S" "X" "W" "WC"

1043500 ER Y ER ER ER
1043500 P Y P P P
1043500 S Y S S S
1043589 ER ER ER ER Y
1043589 T T T T Y
1043589 WC WC WC WC Y
1043637 ER ER ER Y ER
1043637 W W W Y W
1043669 ER Y ER ER ER
1043669 T Y T T T
1043669 ER Y ER ER ER
1043669 C Y C C C
1043669 P Y P P P
1043669 S Y S S S
1043680 EA EA EA EA EA
1043681 ER ER ER ER ER
1043682 ER ER Y ER ER
1043682 X X Y X X
1043684 ER ER ER ER ER
1043685 AU AU AU AU AU
1043701 EA Y EA EA EA
1043701 ER Y ER ER ER
1043701 AU Y AU AU AU
1043701 C Y C C C
1043701 P Y P P P
1043701 S Y S S S
 
Last edited:

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Am I restating this the correct way?


If a cell("x") in Column A is a duplicate of another cell in A, check cell("x") in column B for value="S". If there is an "S", change cell("x") in Column C to "Y". If there is not an "S", copy cell content in Column B to Column C.
 
Upvote 0
Am I restating this the correct way?


If a cell("x") in Column A is a duplicate of another cell in A, check cell("x") in column B for value="S". If there is an "S", change cell("x") in Column C to "Y". If there is not an "S", copy cell content in Column B to Column C.
Yes, that's correct! Thanks for your help. I hope you can solve.
 
Upvote 0
=IF(AND((COUNTIF(A:A,A1)>1), B1="s"), "Y",B1)

this goes in column C


Do you understand how to manipulate this to fit your second set that needs changed?


But this might change all your data in C so im not sure if this is what you want.
 
Upvote 0
Don't use the above code, it will change the data of Non-Duplicates.

So unless you want b1 to equal c1 in all Non-duplicates, this codes will not work for you.
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,109
Members
448,548
Latest member
harryls

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