• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Group column data

V

Vijayarc

Member
Hi Ninja
Below is my sample data,
Column A in one CaseID have multiple ClientID in column B, below are my requirement is,
1. when caseid is not repeated ( have single Client id ) -> My comment be - Good to Off
2. when caseid is repeated ( have multiple Client id) -> my comment be - WIP-Partial Complete

Hope my requirements are clear, please help to do Automate in VBA , it will help to automate for my large data

Case IDClient IDCOMMENT
750408875606162Good to OFF
44830608186729268WIP-partial Complete
44830608142408WIP-partial Complete
44830608181608WIP-partial Complete
44830608182108WIP-partial Complete
44830608231608WIP-partial Complete
44830608306108WIP-partial Complete
44830608456708WIP-partial Complete
395692641576408Good to OFF
221865526598408752WIP-partial Complete
221865526604408WIP-partial Complete
221865526616708WIP-partial Complete
95108508662408Good to OFF
186109889663808WIP-partial Complete
186109889666908WIP-partial Complete
1867292685895521245Good to OFF
 
Hi,​
​
why a VBA procedure as it can be easily achieved with an Excel beginner level formula ?!​
​
 
Hi Marc, , i done by formula =IF(COUNTIF(B:B,B121)>1,"Duplicate","Unique")
My caseID is repeats , here i taken only duplicate cases
please find the below 3 different conditions bases on D & E Column combination
1. If D column = NA and E column = Duplicate , So comment as Good to off
2. If D column <> NA and E column = Duplicate , So comment as WIP
3. If D column is mix of both "NA" and Number for same Caseid , so Comment as WIP-Partial complete

***** please help to solve this conditions in macro , i have 1000 more casesid - very difficult to do manually comments , please help
CASIDClient IDCombinationCDGL LookupCaseID DuplicateComments
222454699​
93700682212224546999370068221NADuplicateGood to offboard
222454699​
93700682872224546999370068287NADuplicateGood to offboard
222454699​
93700682212224546999370068221NADuplicateGood to offboard
222454699​
93700682872224546999370068287NADuplicateGood to offboard
222454699​
937006822122245469993700682212224546999370068221Duplicatewip
222454699​
937006828722245469993700682872224546999370068287Duplicatewip
222454699​
937006822122245469993700682212224546999370068221Duplicatewip
222454699​
937006828722245469993700682872224546999370068287Duplicatewip
222454699​
93700682212224546999370068221NADuplicateWIP- Partial completed
222454699​
937006828722245469993700682872224546999370068287DuplicateWIP- Partial completed
222454699​
93700682212224546999370068221NADuplicateWIP- Partial completed
222454699​
937006828722245469993700682872224546999370068287DuplicateWIP- Partial completed
 
Vijayarc
Your given logic and example do not match. Your logic is
please find the below 3 different conditions bases on D & E Column combination
1. If D column = NA and E column = Duplicate, So comment as Good to off
Click to expand...
Then for the below caseid comment should be Good to offboard instead of WIP- Partial completed.

CASIDClient IDCombinationCDGL LookupCaseID DuplicateComments
22245469993700682212224546999370068221NADuplicateWIP- Partial completed
 
Vijayarc said:
If D column = NA
Click to expand...
Why this is not explained in the initial post ?‼ You can't change the rules like this …​
Anyway you can use the ISNA worksheet function.​
​
Once you get your working formula, as per Excel basics transform your data range as a true Excel table - see Excel help - then​
each time you add a row to this Excel table the formula is automatically added without any VBA procedure !​
​
Before to go on VBA side just better think first of what Excel yet offers …​
​
 
S. Das said:
Vijayarc
Your given logic and example do not match. Your logic is

Then for the below caseid comment should be Good to offboard instead of WIP- Partial completed.

CASIDClient IDCombinationCDGL LookupCaseID DuplicateComments
22245469993700682212224546999370068221NADuplicateWIP- Partial completed
Click to expand...
sorry for confusion , i try to clear.. please

Column A for one CaseID have multiple ClientID in column B

i do vlookup to D column with other file - if all ClientID is NA for same Caseid , then only i can Offboard, otherwise i cant
tricky 2 combination occurs

************** conditions ************
1. if all NA occurs for same Caseid - then comment is Good to Offboard
2. if mix of NA and number comes for same Caseid -i can't offboard so, comment is WIP-Partial

*** hope i make clear , please help for 2 requirements,
 
You must log in or register to reply here.
Back
Top