Index / Match with random multiple rows

HughT

Board Regular
Joined
Jan 6, 2012
Messages
107
Office Version
365
Platform
Windows
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
 

Some videos you may like

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
Joined
Jun 12, 2014
Messages
44,638
Office Version
365
Platform
Windows
How about using Countifs, like
=IF(COUNTIFS(Data!C:C,C2,Data!S:S,"X")>0,"X","")
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,344
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
Joined
Jan 6, 2012
Messages
107
Office Version
365
Platform
Windows
Thank you so much!

Works perfectly!

HT
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
44,638
Office Version
365
Platform
Windows
Glad we could help & thanks for the feedback
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
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...
Top