HELP: Grouped items formula

**chollo1988** · 05-04-2016, 11:37 AM

Hello Everyone i need some help with pointing out specific items in a list of numbers
Column A = I will have one number 123.
Column B = I will have a larger number that includes the variation of the number in column A ex: 123AB
Column C = a specific number that matches exactly whats in column B. EX: 123ab (image name)

SO I need to distinguish which items will have less than 1 image from column C to Column A.
I have thousands of full numbers so Its hard to pick them out without a formula.

forum example.xlsx

**CAntosh** · 05-04-2016, 12:22 PM

If I'm understanding you correctly, a conditional formatting rule might work. If you select A2:A13 and create a conditional formatting rule using the formula:

=ISERROR(MATCH($A2+0,LEFT($C$2:$C$13,3)+0,0))

...the CF rule should highlight all entries in column A that do not have any pairings in column C. In this case, it highlights A10:A13. This highlighting then disappears if you enter "456cc" in C12. Is that what you were looking for?

EDIT: If the length of the number in column A varies, go with:

=ISERROR(MATCH($A2+0,LEFT($C$2:$C$13,LEN($A2))+0,0))

**morevijay1982** · 05-05-2016, 04:49 AM

Cheak out this formula

=(COUNTIFS(C:C,A2&"*")<1)*1

and reply

**morevijay1982** · 05-05-2016, 04:52 AM

Cheak out this formula

=(COUNTIFS(C:C,A2&"*")<1)*1

and reply

0 means having images more than 1 and 1 means no image

**oeldere** · 05-05-2016, 05:00 AM

COUNT d2=if(C2="","",A2) and drag down

CRITERIA e2=if(countif($D$2:$D$17,A2)>=1,"",A2) and drag down

after that just a filter on Criteria of use a pivot table.

See the attached file.