How to get matching records from two columns

[ThrottleWorks]

Hi,

I have data in three columns.
Data is in thousand rows, 50,000 +.
Please see below example for understanding.

Column one has values as Yamaha, Honda, Suzuki.
Column two has values as Carburetor and FI.

Column three has amount

If I filtered on Yamaha, I get values as below.

Cell A2 = Yamaha
Cell A5 = Yamaha
Cell A8 = Yamaha
Cell A11 = Yamaha

Cell B2 = Carburetor
Cell B5 = Carburetor
Cell B8 = FI
Cell B11 = Carburetor

When I filtered on Yamaha, I get both the Carburetor and FI as corresponding values.

So I will consider amount for this set.

If I filtered on Suzuki, I get values as below.

Cell A12 = Suzuki
Cell A15 = Suzuki
Cell A18 = Suzuki
Cell A19 = Suzuki

Cell B12 = Carburetor
Cell B15 = Carburetor
Cell B18 = Carburetor
Cell B19 = Carburetor

In this filter, I did not get both corresponding values, I get only one which is Carburetor.

So will not consider amount for this set.

How do I identify ‘Yamaha’ or ‘Suzuki’ which has both the corresponding values.
Yamaha and Suzuki are just two examples, actual data is in thousands.

If we consider these as two bike companies, actual data has more than 20,000 companies and each company might be repeated from 1 to 200 times.

For example, Yamaha might be present 200 times in column A and Suzuki might be present 5 times in column B.

There can be a third scenario also.
If I filter on KTM. I might get values as below.

A25 = KTM
A50 = KTM
A100 = KTM
A125 = KTM
A150 = KTM

B25 = Disc Pad
B50 = Disc Pad
B100 = Disc Pad
B125 = Disc Pad
B150 = FI

For this also, we cannot consider amount cause each pair must has both the FI and Carburetor values.

In above example, we only have FI but not Carburetor.
If we extend above example and see values as

A175 = KTM
B175 = Carburetor

Then we would have considered amounts for C150 and C175.
Cause in this set, we have both FI and Carburetor for KTM.

It does not matter how many times FI and Carburetor is present.
For KTM, FI can be in 9 rows and Carburetor in 1 row or vice versa.
But we will consider amount for entire 10 rows.
Cause, these ten rows has both the FI and Carburetor at least once.

Can anyone please help me in this.

PS - Will not be able to upload sample file.

 

[shrivallabha]

You have posted a full essay here .

I'd recommend following edits to your question.

Build a sample data grid and post like below so that someone can at least copy it and build by using Text to Columns etc.

Row# | Col A | Col B | Col C
1|Yamaha|FI|1000
2|Suzuki|Carburetor|5000

Then it boils down to a company having two selected values in Column B. In such cases, what exactly do you want to happen with Column C? There should be sample expected output as well.

 

[ThrottleWorks]

Hi @shrivallabha sir, agree with you. Since I am not allowed to upload a file.
An essay was born !

Will try to share details as advised by you. Thanks. Have a nice day ahead.