Is there a way to blank out duplicate cells?

Imagine a table. Column A is username, Column B is a group the user is in.

The table looks like this (not sure how to upload a screen shot:

user            group
Fred            bowling league
Fred            Slate Rock and Gravel
Fred            Friday night glee club
Barney       Bedrock Hot Rod Association
Barney       Friends of Saber Tooth Tigers

The table is sorted by the user column.

Is there a way to export to an excel spreadsheet so it looks like this for readability:

user            group
Fred            bowling league
                    Slate Rock and Gravel
                    Friday night glee club
Barney       Bedrock Hot Rod Association
                    Friends of Saber Tooth Tigers

If not, I can do that in Python. Or I can get off my duff and learn a little Javascript.

Thanks in advance!

You can do this with Offset. See below:

offset.transform (3.1 KB)

It is pretty much the opposite of the Fill transform. Maybe we should add an Unfill transfom to do this?

Clever - as one expects, of course, of EDT.

Added to my EDT recipes.


Perhaps in the Unique Transform there can be another Concat option to insert line breaks rather than commas?

Unique would reduce it to 2 rows. I assume @Amontillado wants 5 separate rows, rather than 2 rows with line breaks within the ‘group’ column.

Yes, that was the need - 5 rows - and the tip worked perfectly.

In the real use, there were about a half dozen tabs in single output file. I think the biggest tab was around 90,000 rows.

Line breaks within a cell would work, too. probably a little cumbersome in this case. The solution with the copy-update-offset gambit worked fine.

1 Like

I needed to do something and JavaScript seemed to be the solution. I hadn’t written a line of code in at least 40 years, but it was pretty straight forward, and this function in EDT is one which I will see as a ‘go to’ solution for many more complex things.

If you don’t know it, JavaScript and HTML DOM Reference will get you going very quickly.


Thanks, yorkeman - bookmarked!

Note that setting duplicate data cells blank is supported by the new Unfill transform from v1.20.1:

1 Like

Nice - and the ability to blank left, right, up, or down is great.

EDT is indispensable. At least once a week somebody wants something extracted from a spreadsheet, or correlated across several data sources. I’m pretty quick with Python. I wouldn’t be completely lost without EDT - but I’d be working harder.

Many thanks.

1 Like