If number only appears once, bring back what it says in this column

[Loupiee]

Hi,

I am starting to pull my hairs out with this! I have all the data on the 'Summary' sheet. On sheet 1 in column B ("From"), I need it to match the number to the number on the 'Summary' sheet, and if it says "Originates" in column C, to bring back column A. I managed to do an IF statement but it brings back errors randomly and I could really do with this in VBA.

Once the above is done, I need a similar thing for column C on 'Sheet 1' except this time it's if column C on the 'Summary' sheet says "Terminates". But there's also a caveat here... if the number only occurs once in the data, and if it says "Originates" to bring back the name.

Hopefully this makes sense...

[oeldere]

B2 =IF(INDEX(Summary!$A$1:$C$462,Match($A2,Summary!$B$1:$B$462,0),3)="ORIGINATES",INDEX(Summary!$A$1:$C$462,match($A2,Summary!$B$1:$B$462,0),1),"")

For the result in C2 you have to spell it out for me.

When do you want to get which answer and why?

[Loupiee]

That's brilliant, thank you!

For C2, it needs to do like B2 but bring back ones that say "TERMINATES" on the 'Summary' sheet but there's an and - it also needs to look for non-duplicates in column B2 on the 'Summary' sheet (about a third are duplicates) and if column C (Summary sheet) = ORIGINATES, bring back the value in column A to 'Sheet 1' column C.

[oeldere]

Please poste and show a couple of answers in your Excel file.

[Loupiee]

Attachment 676530

In column A, I have put a conditional formatting in place to highlight the duplicate numbers. For all the ones that aren't duplicates and say "ORIGINATES" (in column C of the Summary sheet), the file number goes in column C (on sheet 1).

The cells I've coloured orange, I used your formula above but changed "ORIGINATES" to "TERMINATES".

Thank you for helping me with this

[oeldere]

"Invalid Attachment specified. If you followed a valid link, please notify the administrator."

[Loupiee]

Does this one work?

[oeldere]

That is not a excel file I could work in.

So it is not helpfull for me.

[Loupiee]

Sorry, excel file attached

[oeldere]

explain again,

In sheet summary:

you want to find the first duplicated value in column B?

After that you would like to find the next value (in column C)?

And when comes "TERMINATES" is relevant in the formule.

I do not understand what the criteria are and in which order.

[Loupiee]

Think it'll help if I explain what this data is a bit. The numbers reference a line that is going from one drawing to another. It's a bit like a map - say the number starts at point A on drawing 1, and carries on on drawing 2 at point B and then terminates on drawing 3 at point C. In the data it'll show like:

Column A | Column B | Column C
00565 | 1020-18-001 | ORIGINATES
00565 | 1020-18-002 | CONTINUES
00565 | 1020-18-003 | TERMINATES

There are some numbers (in column A) that only appear once, saying ORIGINATES in column C. They have no carry on in any other drawings and so will not duplicate in the data. This is one of the things that column C on sheet 1 needs to bring back.

If a number has TERMINATES in column C, it essentially means that it is a duplicate - there will be in the data that number elsewhere saying ORIGINATES.

So, column C on sheet 1 needs to bring back both. It's not an IF not this bring back this. It's an AND in some way. But it needs to work through the list one by one as I have the data dump which then get splits out into about 10 sheets (this is just one of them) using the Advanced Filter.

[oeldere]

Ok that's usefull information, but in the example, what is the expected result for column C
1020-18-001 ORIGINATES ?

or

1020-18-002 CONTINUES ?

or

1020-18-003 TERMINATES ?

[Loupiee]

In the example it would be:
1020-18-003 TERMINATES that comes back. The number (column A) makes it that it's not a unique number/is a duplicate.

[oeldere]

But then I think I don't understand.

When it is TERMINATES it is always a duplicated one, so we only have to look at Terminates?

So the order is look if there is Terminates, then the result is 1020-18-003.

If it is not Terminates, it has to be 1020-18-001.

Is this statement correct?

[Loupiee]

Sometimes it says CONTINUES, or CUSTOM and when that happens the 'number' will be a duplicate and those are not to be brought back.

[oeldere]

Summary D2=IF(And(C2<>"Terminates",countif($B$2:$B$46,$B2)>1),"yes","") and drag down.

Sheet 1 D2 =if(INDEX(Summary!$A$1:$C$462,Match($A2,Summary!$B$1:$B$462,0),3)="TERMINATES",INDEX(Summary!$A$1:$C$462,match($A2;Summary!$B$1:$B$462,0),1),if(VLookup(A2,Summary!$B$2:$D$461,3,0)="yes","",if(INDEX(Summary!$A$1:$C$462,Match($A2;Summary!$B$1:$B$462,0),3)="ORIGINATES",INDEX(Summary!$A$1:$C$462,match($A2,Summary!$B$1:$B$462,0),1),""))) and drag down (green cells).

See the attached file.

[Loupiee]

Thank you very very much! Worked like a dream

[oeldere]

It was more difficult to understand the question than, in this case, produce the answer.

Glad I could help.

If you are satisfied with the solution, please mark the question solved.

You can add reputation by clicking on the star * add reputation.