Unique rows but a bit more complicated than that!

HelpMeExcelExperts

New Member
Joined
May 16, 2021
Messages
4
Office Version
  1. 365
Hi, before I start I just want to say thank you to all the volunteers that do work on this site. I often benefit from answers provided here and it is my first time requiring to post something. Looking forward to learn!

Here is my issue.

I need to make sure
1) That I keep unique rows based on country code and country. This means that if there is:

Country CodeCountry
61Australia
61Singapore
61Australia
63Singapore
64Singapore

The only row to remove would be the third row. This is simple as I can simply go UNIQUE(Table, FALSE, FALSE), but there are other added conditions based on the other columns.

2) We want to keep unique information, AND also possibly merge or remove rows that are not informative. For instance, for the same country code, country and flight time, we want to keep the one that has the more complete information. But if there are contradicting evidence then we also want to keep them. For example:

Country CodeCountryFlight TimeSale On?Type
61Australia35NoBusiness
61Australia35
61Australia35Yes
61Australia20Economy
61Australia20No
61SingaporeBusiness

In this case, I want:

Country CodeCountryFlight TimeSale On?Type
61Australia35NoBusiness
61Australia35Yes
61Australia20NoEconomy
61SingaporeBusiness

Here is my sample data.

Country CodeCountryFlight TimeSale On?Type
61Australia35NoBusiness
61AustraliaBusiness
61Australia17Business
61Australia35Business
61AustraliaEconomy
60New Zealand11YesBusiness
60New Zealand11NoBusiness
60New Zealand11UnsureBusiness
60New Zealand11
61SingaporeEconomy
I want to keep:

Country CodeCountryFlight TimeSale On?Type
61Australia35NoBusiness
61Australia17Business
61AustraliaEconomy
61SingaporeEconomy
60New Zealand11YesBusiness
60New Zealand11NoBusiness
60New Zealand11UnsureBusiness

I hope that makes sense. I have a feeling it might not be possible (though would love to be proved otherwise!), so please let me know if it is not possible as well.
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Tried my best to fix the formatting.. sorry there is a max of ten rows, and my laptop is governed by company policy so cannot download the XL2BB, sorry.

Here is my issue.

I need to make sure
1) That I keep unique rows based on country code and country. This means that if there is:

Country CodeCountry
61Australia
61Singapore
61Australia
63Singapore
64Singapore
The only row to remove would be the third row. This is simple as I can simply go UNIQUE(Table, FALSE, FALSE), but there are other added conditions based on the other columns.

2) We want to keep unique information, AND also possibly merge or remove rows that are not informative. For instance, for the same country code, country and flight time, we want to keep the one that has the more complete information. But if there are contradicting evidence then we also want to keep them. For example:

Country CodeCountryFlight TimeSale On?Type
61Australia35NoBusiness
61Australia35
61Australia35Yes
61Australia20Economy
61Australia20No
61SingaporeBusiness

In this case, I want:

Country CodeCountryFlight TimeSale On?Type
61Australia35NoBusiness
61Australia35Yes
61Australia20NoEconomy
61SingaporeBusiness

Here is my sample data.

Country CodeCountryFlight TimeSale On?Type
61Australia35NoBusiness
61AustraliaBusiness
61Australia17Business
61Australia35Business
61AustraliaEconomy
60New Zealand11YesBusiness
60New Zealand11NoBusiness
60New Zealand11UnsureBusiness
60New Zealand11
61SingaporeEconomy

I want to keep:

Country CodeCountryFlight TimeSale On?Type
61Australia35NoBusiness
61Australia17Business
61AustraliaEconomy
61SingaporeEconomy
60New Zealand11YesBusiness
60New Zealand11NoBusiness
60New Zealand11UnsureBusiness

I hope that makes sense. I have a feeling it might not be possible (though would love to be proved otherwise!), so please let me know if it is not possible as well.
 
Upvote 0

Forum statistics

Threads
1,213,522
Messages
6,114,112
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top