# Thread: Classify Data Based Upon Cell Value

1. ## Classify Data Based Upon Cell Value

Hi, looking for help categorizing data. I think it can be done w/formulas but I don't know how. See attached spreadsheet. All help is appreciated!!!

Mitch

2. First shot.

I unmerged the cells in the J column and placed this formula in J2 and filled down.

=IF(A1=A2,"",IF(COUNTIFS(A:A,A2,J:J,J2)=COUNTIF(\$A :\$A,INDIRECT("A"&ROW())),"NONABC","Shared"))

Other than using a Pivot Table, there must be an easier way...just didn't see it initially.

I always try for what works first, then try for what is slick.

3. Originally Posted by kweaver
first shot.

I unmerged the cells in the j column and placed this formula in j2 and filled down.

=if(a1=a2,"",if(countifs(a:a,a2,j:j,j2)=countif(\$a :\$a,indirect("a"&row())),"nonabc","shared"))

other than using a pivot table, there must be an easier way...just didn't see it initially.

I always try for what works first, then try for what is slick.
I tried in a different sheet and it didn't work... I will check into and come back to you. Thanks.

4. Hi Mitch

see kweavers formula in attached file.

zeddy

5. I've also attached my sheet which used conditional formatting to block the cell ranges.

6. Originally Posted by kweaver
I've also attached my sheet which used conditional formatting to block the cell ranges.
Ok. I am getting there. SO, what if it has one more condition in it that I didn't list before? How to make this work? See bottom of this in yellow... I didn't see a button to reattach file...

aa002808 FM FLAVOR MATERIAL 198 10,000.00 LB 0.491 LB 0 ABCCANADA
aa002809 FM FLAVOR MATERIAL 198 10,000.00 LB 0.491 LB 0 ABCCANADA NONABC SHOULD SAY ABCCANADA
aa002809 FM FLAVOR MATERIAL 198 10,000.00 LB 0.491 LB 0 ABCCANADA
aa002809 FM FLAVOR MATERIAL 198 10,000.00 LB 0.491 LB 0 ABCCANADA
aa002809 FM FLAVOR MATERIAL 198 10,000.00 LB 0.491 LB 0 ABCCANADA
aa002809 FM FLAVOR MATERIAL 198 10,000.00 LB 0.491 LB 0 ABCCANADA
aa002809 FM FLAVOR MATERIAL 198 10,000.00 LB 0.491 LB 0 ABCCANADA

7. Attach your new file. Not sure what the other condition is.

8. Originally Posted by kweaver
Attach your new file. Not sure what the other condition is.
should be in here now.

10. KW. Yes, this gets it... How does this magic work???

11. Well...

=IF(A1=A2,"",IF(COUNTIFS(A:A,A2,J:J,J2)=COUNTIF(\$A :\$A,INDIRECT("A"&ROW())),J2,"Shared"))

I checked to see if in a block of cells that had the same component code and identical ABC? codes (from the J column) and if that number of cells matched the number of cells that were the same as in the corresponding J cell. If so, I inserted the cell data; otherwise, inserted "Shared" in the cell.

The first test for A1=A2 was to see if the cells needed to be checked (i.e., only the first cell in a block needs to be checked) because the other cells either all match or don't (Shared).

To put the borders on, check the formula in the Conditional Formatting.

HTH.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•