Index / Match with random multiple rows

HughT

Board Regular
I have a worksheet called Data. Each row relates to a customer repair. In column C is a part number (3 digit). There are many different part numbers distributed on different rows. If the customer rings to chase the repair the user puts an X in column S against that customer, so there can be many Xs in column S against many part numbers, but not all rows will have an X in column S.

There is a separate worksheet called Repair Log. This lists each part number in column C. In column M of this worksheet I want to an X to appear if there has been a single X entered in any row on the Data worksheet for that part number, ie there is immediate visibility that one chaser call has been made irrespective of which customer it was.

I tried VLOOKUP but that only finds the first row and stops. It looks like Index / Match might work but I have got stuck!

Many thanks

HT

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Fluff

MrExcel MVP, Moderator
=IF(COUNTIFS(Data!C:C,C2,Data!S:S,"X")>0,"X","")

Special-K99

Well-known Member
Try

in 'Repair Log'!M1
=IF(COUNTIFS(Data!C\$1:C\$1000,C1,Data!S\$1:S\$1000,"X")>0,"X","")
and copy down the column

So for each row in 'Repair Log' column M
count the number of Xs in column S of Data sheet where the part number matches the code in column C
If this result is greater than zero then there is at least one X in column S for the part number mentioned on the Repair Log for that row.

Last edited:

HughT

Board Regular
Thank you so much!

Works perfectly!

HT

Fluff

MrExcel MVP, Moderator
Glad we could help & thanks for the feedback

1,106,169
Messages
5,509,569
Members
408,743
Latest member
1245585

This Week's Hot Topics

• Turn fraction around
Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
• TIme Clock record reformatting to ???
Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
• TextBox Match
hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
• Using Large function based on Multiple Criteria
Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
• Can you check my code please
Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
• Combining 2 pivot tables into 1 chart
Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...