To get Correct Data Number after 3 stage validation

**Nasir Choudhary** · 01-12-2013, 09:50 AM

Hello Friends,

I have a huge data which has 4 columns. Need formula for the 4th Column

1st - Data Source (This is the deciding factor of the Correct Number. It has 2 values: SERVICE AND SALES.)

2nd - Master Number (These are unique numbers)

3rd - Slave Number (These are Duplicate Numbers)

4th - Number after validation (The correct number to be populated using formula which will be decided based on Data Source column information)

NEED HELP TO GET THE CORRECT NUMBER BASED OF DATA SOURCE COLUMN INFORMATION.

In slave number column, there are duplicate numbers which means 2 records with number 111. And if in the Data source column 1st records is SALES & 2ND RECORD IS SERVICE. Only then I want the Master Number with maximum digit (A111) to be populated for both the records in the Number After Validation column.

Duplicate records with both SALES or SERVICE IN Data Source column, need NO CHANGES.

Can anyone help.

**martindwilson** · 01-12-2013, 10:24 AM

Attach a sample workbook.

When you are in Advanced reply, click on the paperclip to open the upload window.

View Pic

**rcm** · 01-12-2013, 10:24 AM

could you send a sample?

**Nasir Choudhary** · 01-12-2013, 10:34 AM

Originally Posted by rcm

could you send a sample?

Please check the attached file

**rcm** · 01-12-2013, 12:14 PM

try the macro included

**martindwilson** · 01-12-2013, 01:29 PM

you could try in d2
=IF(OR(AND(A1="service",A2="sales"),AND(A2="service",A3="sales")),"A"&C2,C2)

**Marcol** · 01-12-2013, 02:24 PM

Or in case "Sale" can come before "Service" in Column A, or they aren't together in the list.

Formula:

=IF(AND(SUMPRODUCT(($A$2:$A$10="Service")*($C$2:$C$10=C2))>=1,SUMPRODUCT(($A$2:$A$10="Sales")*($C$2:$C$10=C2))>=1),"A"&C2,TEXT(C2,"#"))

To get Correct Data Number after 3 stage validation

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

To get Correct Data Number after 3 stage validation

Re: To get Correct Data Number after 3 stage validation

Re: To get Correct Data Number after 3 stage validation

Re: To get Correct Data Number after 3 stage validation

Re: To get Correct Data Number after 3 stage validation

Re: To get Correct Data Number after 3 stage validation

Re: To get Correct Data Number after 3 stage validation

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions