Combine Cells with Unique Identifier

Joanna Collins · 09/24/21

I have a sheet that allows users to scan certain components online

They may scan the same tracking number, but different components online at several different times in the process. Is there a way to combine all of the same tracking numbers to reflect the online status of each component? Example row #1 the glass came online, row #2 the wood came online. (same tracking number) I would like to have a record that reflects both glass and wood online in the same row so I could do an index/match to the tracking # on another sheet.

Currently my index/match only picks up the first row for that tracking number. It shows online for the glass, but not the wood since the glass is the first record for that tracking #. Hoping there would be a way to use collect and a helper sheet to combine the tracking number into one record that would reflect glass and wood online in one row....

Thank you!

Genevieve P. · 09/24/21

Hi @Joanna Collins

Will the values only ever by "ONLINE" or blank? I'm wondering if instead of using an Index(Match, if we could use a COUNTIFS cross sheet formula, and if the count is 0 return blank or if it's 1 return "ONLINE".

So, first we would do this:

COUNTIFS({Tracking Number Column}, [TRACKING #]@row, {Glass Column}, "ONLINE")

And then embed that in an IF statement, like so:

=IF(COUNTIFS({Tracking Number Column}, [TRACKING #]@row, {Glass Column}, "ONLINE") = 0, "", "ONLINE")

You would need to create a new formula for each column reference, like so:

=IF(COUNTIFS({Tracking Number Column}, [TRACKING #]@row, {Wood Column}, "ONLINE") = 0, "", "ONLINE")

Let me know if this makes sense or if you'd like to see screen captures!

Cheers,

Genevieve

Genevieve P. · 09/24/21