[Solved] How to remove duplicates.... with a twist

Discuss the spreadsheet application

[Solved] How to remove duplicates.... with a twist

Postby thefonz22 » Fri Apr 03, 2020 4:32 pm

Not sure if this is possible or not.

I have a list of businesses in my directory spreadsheet. I need to select 1 business from each category, and remove the rest. The information we have for each business is "business name", "email", "address" and "URL".

I want to keep one business from each category that has all 4 of these categories populated! However if there isn't a category(that has all 4 populated) then I would like to select (and remove the others) the business that has 3 categories populated. If this is not possible then 2, and 1.

If there is a tie. EG a bunch of businesses from the same category have all 4 bits populated, then it randomly picks one and then removes the other businesses.

Hope I am explaining myself ok. Please see screenshot below of a typical category.
Attachments
twist.PNG
twist.PNG (11.57 KiB) Viewed 246 times
Last edited by MrProgrammer on Sat Apr 11, 2020 6:07 pm, edited 1 time in total.
Reason: Tagged ✓ [Solved]
OpenOffice 3.1 on Windows 8
thefonz22
 
Posts: 10
Joined: Tue Aug 05, 2014 9:35 pm

Re: How to remove duplicates.... with a twist

Postby FJCC » Fri Apr 03, 2020 4:56 pm

Here is one solution. The original data are in A1:E12. I added the Flag value in column F that counts how many fields are filled. I then sorted on Business Category and Flag (Descending). The result of that is shown starting in column H. I then added the Order column that numbers the entries in each Business Category sequentially. You can filter that column using the menu Data -> Filter for values of 1 and copy that result to a convenient place.
Attachments
OnePerCategory.ods
(10.72 KiB) Downloaded 13 times
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7680
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: How to remove duplicates.... with a twist

Postby thefonz22 » Fri Apr 03, 2020 5:34 pm

You are a true hero!

Works a charm. I'll be using this for the next few years and appreciate this!!!
OpenOffice 3.1 on Windows 8
thefonz22
 
Posts: 10
Joined: Tue Aug 05, 2014 9:35 pm

Re: How to remove duplicates.... with a twist

Postby thefonz22 » Fri Apr 03, 2020 5:54 pm

Just noticed the "order" column doesnt work.

Any suggestions to get the order column to work and then its perfect.
Attachments
Capture3.PNG
OpenOffice 3.1 on Windows 8
thefonz22
 
Posts: 10
Joined: Tue Aug 05, 2014 9:35 pm

Re: How to remove duplicates.... with a twist

Postby FJCC » Fri Apr 03, 2020 6:22 pm

The Order column does not sort the data, it shows which row is the first in each Business Category. If you add data, you have to select the whole table H1:N12 and sort it on Business Category and Flag (Descending). Then you can use the filter in the Order column to show only the rows where Order = 1.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7680
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA


Return to Calc

Who is online

Users browsing this forum: No registered users and 14 guests