Calculating multiple duplicate values between two arrays

Hello,

I have two tables of numbers, each row in table 'A' represents a list of numbers that are part of an asset group and the second table is all available assets by classification. I have already applied a simple conditional format to highlight duplicates within table 'A' that match the table of a specific asset group (C-10) in table 'B' but would like to upgrade this to a formula.

Problem 1: Instead of using Excel's build in duplicate condition formatting, can I use a formula so I can create a drop down list for each asset class and the condition formatting for duplicated changes based on the class selected? As info, there will only ever be one duplicate in table 'A' compared to table 'B'

Problem 2: I would like a formula that can sum how many matches are in each individual row of table 'A' to whichever asset group in table 'B' is selected based on the drop down list.

I attached a screen shot as I cannot attach an excel file for some reason.

Thank you for anyone who is able to take time to help me with this!

Thanks - I did try but I believe the issue is my internal work firewall preventing me from uploading.

Hello,

I have build a spreadsheet that takes an asset allocation list (Table A) and compares it to the 'Dynamic list' which is filtered based on which asset class is selected from the dropdown at the top of table 'A'. The ''Dynamic List' is a filtered version of the master list, Table 'B'. The asset allocation info is fed from the work sheet tab. The reason for this is not apparent in the sample I uploaded, but it is an important feature for the main working file I will be applying these changes to. I tried several tricks for a countif & match formula for a condition format formula, but I was unable to get anything to work for the entire Table 'A' table. I removed them to clean up the example file. I suspect there is a complication with having Table 'A' as a spilled row and the 'Dynamic list' being a spilled column.

I would like to fix the conditional formatting currently in place for Table 'A' to highlight asset numbers that match the 'Dynamic List', and changes based on which filter is selected. The built in basic duplicate condition formatting works, but it also highlights blanks which I would like to avoid.

Can anyone of you fantastic folks help clean up the conditional formatting to only highlight direct matches to the dynamic list and ignore blanks?

Thank you!

Originally Posted by bsalv

i hope i understood the question

Hello Bsalv - You understood the question and provided exactly what I needed. Thank you for taking the time to do that!

For any future readers:

My original issue was creating a column that calculated the total number of matches between a fixed table and a dynamic spilled list. This was achieved using this formula: =SUM(COUNTIF($C6:$P6,$T$5#),0) (Cell references are specific to the file I uploaded, and found in column 'R'.

The second issue was conditional formatting to then highlight the cells in in Table 'A' that matched the cells in the dynamic list, and also ignore blank cells. Bsalv solved this by using a formula in conditional formatting: =AND(C6<>"",COUNTIF(Dyn_List,C6))

I have so much appreciation for those who take time to follow these forums and share their time to help others. Thank you.

Originally Posted by JeteMc

Is your file larger then 1000 KB? If so, you could produce a sample file that has 10 or so rows in table A and 10 or so rows in table B. That should produce a small enough file to attach and also give us enough data with which to identify some duplicates between table A and each column in table B. We can then attempt to write a formula that will work for the original tables. I am somewhat concerned about what appears to be a merged cell in table B, AA2.
Let us know if you have any questions.

Thanks for the feedback. I know merged cells are taboo, but my example is a small portion of my master file I created to help solution my issue. I took the liberty to help it present better since the merged cells were not part of the formula array.