HelpMeExcelExperts
New Member
- Joined
- May 16, 2021
- Messages
- 4
- Office Version
- 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:
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:
In this case, I want:
Here is my sample data.
I want to keep:
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.
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 Code | Country |
61 | Australia |
61 | Singapore |
61 | Australia |
63 | Singapore |
64 | Singapore |
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 Code | Country | Flight Time | Sale On? | Type |
61 | Australia | 35 | No | Business |
61 | Australia | 35 |
61 | Australia | 35 | Yes | |
61 | Australia | 20 | Economy | |
61 | Australia | 20 | No | |
61 | Singapore | Business |
In this case, I want:
Country Code | Country | Flight Time | Sale On? | Type |
61 | Australia | 35 | No | Business |
61 | Australia | 35 | Yes | |
61 | Australia | 20 | No | Economy |
61 | Singapore | Business |
Here is my sample data.
Country Code | Country | Flight Time | Sale On? | Type |
61 | Australia | 35 | No | Business |
61 | Australia | Business | ||
61 | Australia | 17 | Business | |
61 | Australia | 35 | Business | |
61 | Australia | Economy | ||
60 | New Zealand | 11 | Yes | Business |
60 | New Zealand | 11 | No | Business |
60 | New Zealand | 11 | Unsure | Business |
60 | New Zealand | 11 |
61 | Singapore | Economy |
Country Code | Country | Flight Time | Sale On? | Type |
61 | Australia | 35 | No | Business |
61 | Australia | 17 | Business | |
61 | Australia | Economy | ||
61 | Singapore | Economy | ||
60 | New Zealand | 11 | Yes | Business |
60 | New Zealand | 11 | No | Business |
60 | New Zealand | 11 | Unsure | Business |
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.