Combining data from duplicate entries

Jason Close

I have a form designed to send to end users of printing devices in an office to collect data on how they use their devices. Nothing is previously known about the devices or users.

I want to then take that data and combine any entries that have the same serial number into 1 row of data.

The Serial number is part of the data collection.

If the data column is of checkbox type, where the serial numbers are duplicate, I want it to compare each cell in that column for only matching serial numbers and return a check if any single cell from all those cells has a check already, if they are all unchecked I would want it to remain unchecked. Essentially if any one person needs this feature then it gets recorded regardless of the other answers.

If the data column is a text entry, where the serial numbers are duplicate, I want it to combine those cells into one cell with a separator between each entry. (except my fax column)

Essentially I want to take data that looks like this:

SSDup1.jpg

And get it to look like this:

SSDup2.jpg

Without manual intervention.

Is this possible?

Sean Morgan

Hello @Jason Close ,

After reading your post, I can see that you've already made a greate development into your Formula, but are still facing issues around a duplication Formula.

You are correct in the fact that it would be difficult to remove duplicate data once its been identified. I'm unable to find a fitting solution in where the rows could be moved to another Sheet if duplicated, and then to have data match that of pre existing data on an existing row E.G If certain checkboxes are checked on row and not another, for them to then sync between each other.

With this, you may wish to raise an Enhancement Request using the 3 horizontal line / Hamburger menu at the top left of this page.

Let me know if you have any questions.

Regards

Sean

Jason Close

I am using 3 sheets with this, one that is storing user data, one storing IT data and one that is combining data from both sheets.

I have figured out how to handle the checkbox columns using this IF and COUNTIFS formula.

=IF(COUNTIFS({User SN}, [Serial Number]@row) > 0, IF(COUNTIFS({User SN}, [Serial Number]@row, {User Colour}, 1) > 0, "Colour - Yes", "Colour - No"), "")

This checks for matching serial numbers between the 2 sheets and if there is a match if will then count the number of times the Colour column has a checked value. If both conditions are met then "Yes", if there is a matching SN but no colour then "No"... if there is no matching serial number the cell is blank.

The part I'm having issues with now is combining multiple cells of text entry where the serial number matches.

I think I have to use the JOIN and/or COLLECT formulas, just not sure how to implement it.

Jason Close

I have this formula now

=JOIN(COLLECT({User SPrint}, {User SN}, INDEX({User SN}, MATCH($[Serial Number]@row, {User SN}, 0))), " / ")

It is giving me an output of

Envelopes Labels / / / Colour Paper

This was originally entered via Multi-select dropdown, it is matching 4 different rows based on matching serial number.

The 4 rows are

Envelopes Labels

[Blank]

[Blank]

Colour Paper

Is it possible to adjust this to remove blank entries?

Is it possible to adjust this to remove duplicate entries in the column I want to combine?

Jason Close

I have this formula now that is removing blank entries. Only thing left would be to handle duplicates but I think I'm not going to pursue that further. I don't really care if there are duplicates and it doesn't seem all that easy to figure out.

=IFERROR(JOIN(COLLECT({User SPrint}, {User SN}, INDEX({User SN}, MATCH($[Serial Number]@row, {User SN}, 0)), {User SPrint}, NOT(ISBLANK(@cell))), " / "), "")

This will join a column of text into one cell based on a unique ID that has multiple entries(rows) and will not add any blank entries. If there are any errors (no matching SN in that range) it will just leave the cell blank.

Sean Morgan

Hello @Jason Close ,

After reading your post, I can see that you've already made a greate development into your Formula, but are still facing issues around a duplication Formula.

You are correct in the fact that it would be difficult to remove duplicate data once its been identified. I'm unable to find a fitting solution in where the rows could be moved to another Sheet if duplicated, and then to have data match that of pre existing data on an existing row E.G If certain checkboxes are checked on row and not another, for them to then sync between each other.

With this, you may wish to raise an Enhancement Request using the 3 horizontal line / Hamburger menu at the top left of this page.

Let me know if you have any questions.

Regards

Sean