Excel (Mac) Formula - Group Like Items and Search Each Row

user_11053343 0
Viewed 55

I've got a really huge excel file (14mb) that I need to remove certain rows based on criteria and can't figure out what the formula would look like to accomplish what I need.

Here's an example of the data:

ID             TYPE          MATCH
12345          CAR            
12345          TRUCK
54321          PLANE
54321          BOAT
12345          BOAT

For this example, what I need to do is group all duplicative IDs and then search through each of the rows of that group to see if one of them is a car.

The output should look like this:

ID             TYPE          MATCH
12345          CAR           TRUE  
12345          TRUCK         TRUE
54321          PLANE         FALSE
54321          BOAT          FALSE
12345          BOAT          TRUE

Since ID 12345 has a car in the group, all of those rows get marked as true. Likewise, as 54321 does not have a car in the group, all of those rows get marked as false.

I want to do this entirely using a formula as I need to modify the search parameters to pair down the data.

excel-formula excel
1 Answers

My understanding of your question is like this:

enter image description here

and this would be the formula:

edit:

If you just want TRUE/FALSE you can enter this in C2 (version w/o LET):

=BYROW(A$2:A$6;LAMBDA(rowID;SUMPRODUCT(--ISNUMBER(SEARCH(FILTER(B$2:B$6;A$2:A$6=rowID);"Car")))>0))

.. and without spilling:

=SUMPRODUCT(--ISNUMBER(SEARCH(FILTER(B$2:B$6;A$2:A$6=A2);"Car")))>0

edit 2 as a fast and simple solution (not optimized):

create a helper column E to list all IDs that has “Car” as type starting with E2:

 =IF(B2="Car";A2;0)

assuming “0” is not a valid ID as dummy for the other rows.

In column C try to match ID with helper column:

=ISNUMBER(MATCH(A2;E$2:E$6;0))

enter image description here

user_189587520
Related