How to look up a text word within multiple cells with Specific Criteria in EXCEL

[Scuba]

Hello :hi:,

Hopefully someone can help me please, I have a very large data set, which I have shorten down in the attached file.

I require a formula that will first look for the text 'Truck 1' within each line that has the same SCN number in column F, if all rows have the text 'Truck 1' then return "Yes", else "no".

If possible the formula should take into account that the text 'Truck 1' could also be spelt TRUCK 1, TRUCK1, Truck1 or Truck 1 i.e. upper or lower case with/without space before the number, as I cannot guarantee that the people inserting the text will do it exactly the same way each time, despite being told how to do it :banghead:

I will be substituting the Yes/No with a further formula once I have the basics here working, I'm just trying to keep this request as simple as possible at this stage.

I hope I have explain this well enough?

Regards

Richard

[Aflatoon]

=COUNTIFS($F$4:$F$23,F4)=COUNTIFS($F$4:$F$23,F4,$C$4:$C$23,"TRUCK*1")
assuming you don't also have Truck 11 or other numbers (besides 1) ending in 1?

[Scuba]

Perfect and thank you for very quick response, this works great.

Next stage is for me to now add 2 different formulas, depending on a True or False return.


=IF(COUNTIFS($F$2:$F$21,F2)=COUNTIFS($F$2:$F$21,F2,$C$2:$C$21,"TRUCK*1")=TRUE,"Yes","No")

If argument is 'True' then I need to substitute "Yes" above for a formula that then adds up the total numbers in column D, which have matching/same SCN number in column F.

If argument is 'No' then I need to add up the total numbers in column D, which have matching/same Booking number (Column E & matching/same SCN number in column F.

The reason for the difference here is because when I pivot this data, if I have more then 1 truck (as in row 14 & 15), the pivot returns the correct data, however if I have only 'Truck 1' shown against a given BKG & SCN number, the pivot totals the same number across all lines, which gives incorrect values.


For example rows 14 & 15 moved as 1 shipment (same SCN number) but required 2 trucks, 1 with 33 pallets and 1 with 4 pallets.

Where as Rows 16 to 21, also moved as 1 shipment (same SCN number) but only required 1 truck with total 4 pallets, yet my pivot is showing 24!

So I am trying to find a way that returns the correct total pallet count

I have updated the attached spreadsheet with formulas for reference.

regards

Richard

[Aflatoon]

Why can't you just use column D for the values in the pivot? They appear to total correctly.

[Scuba]

Lol, this is a perfect example of 'over thinking things' :crying:

Sometimes you can't see the wood for the tree's :banghead:

Thank you and yes, this solves things.:bow:

[Aflatoon]

:) I don't really do complicated.