Two Lists, One Colum, Combine without Dups

zendog1960

zendog1960

Active Member
Joined
Sep 27, 2003
Messages
459
Office Version
  1. 2019
Platform
  1. Windows
I have looked through the board and found many solutions to combining list in rows with multiple data columns. This however does not offer me any insight to what I want to do. It seems so simple that it should be rather easy but I have trouble wrapping my brain around this one.

I would like to keep the solution into formula form if possible.

Example:

I have 20 names. In each of the two ranges listed below, the names may or may not appear in both lists. The first range may only have 11 names listed where the second range may have 11 names out of the 20 but two of the 11 are different from the first range.

Column D
Ranges D4:D23, D28:D47

The two above ranges are first names only.

Output Expectation:

Target Range D52:D71

I would like to combine the two lists and not show any of the duplicates. If for example Tom is on both lists then Tom would only show up once. However, Frank may be in list one but not in two and Billy might not be in list one but in two yet both names will be listed in the target range.

I know clear as mud right?

Anyone have a solution in formula formate or does this absolutly require VBA? If a formula solution is available, can you please enlighten me?

Thanks in advance for your help into this solution.

Zen
 
Last edited:

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Sort by date Sort by votes
Any quick ideas here?

To simplify the issue:

Range one: D4:D23 = List of names
Range two: D28:D47 = List of names

Range Three: D52:D? (However long) = Combining list one and two with no duplicated.

Thanks
 
Last edited:
Upvote 0
Maybe This will help Clarify?

<html><head><title>Excel Jeanie HTML</title></head><body>
Excel Workbook
ABCDE
1List AList B*Combined*
2ReidReid*Reid*
3JustinJustin*Justin*
4JoelJoel*Joel*
5KellyKelly*Kelly*
6JimTony*Jim*
7TomTom*Tom*
8AndrewAndrew*Andrew*
9PetePete*Pete*
10RonRon*Ron*
11MattMatt*Matt*
12DannyDanny*Danny*
13--*Tony*
14***-*
15*****
Sheet1


</body></html>

Any suggestions on how to get the list in colum D to populate with unique values?
 
Upvote 0
zendog1960 said:
Maybe This will help Clarify?

Excel Workbook
ABCDE
1List AList B*Combined*
2ReidReid*Reid*
3JustinJustin*Justin*
4JoelJoel*Joel*
5KellyKelly*Kelly*
6JimTony*Jim*
7TomTom*Tom*
8AndrewAndrew*Andrew*
9PetePete*Pete*
10RonRon*Ron*
11MattMatt*Matt*
12DannyDanny*Danny*
13--*Tony*
14***-*
15*****
Sheet1




Any suggestions on how to get the list in colum D to populate with unique values?
Click to expand...

Hi Zen, if you allow a column of dummy fig in Column C, and E. There is a quick and dirty way to achieve it...

In C1, input "dummy"
In C2:C15, input 0

Then select D1
Go to Data -> Consolidate
Select Function "Sum"
Add in the references
Sheet1!$A$1:$A$15
Sheet1!$B$1:$C$15

Check the Use Labels in
Top Row
Left Column

OK

Delete the dummy columns afterwards...

I know this may not be a nice solution for you... but you may try if you have no other options.

Hope it helps.
 
Upvote 0
ok I can work with that but is there a way to have the "consolidation happen automatically?
 
Upvote 0
ok I found that this works well and does what I want. Just information for the masses!

Code: 
Private Sub Worksheet_Activate()
    Range("F79:G119").Clear
    Range("F79").Select
    Selection.Consolidate Sources:=Array("'WK 1 Stats'!R79C3:R99C3", "'WK 1 Stats'!R79C4:R99C5"), Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=False
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Question Question
Take data from 2 sheets, search and combine
Replies
1
Views
244
alansidman
alansidman
C
  • Question Question
Comparing Grouped Pivot Table Data
Replies
1
Views
111
cwhaley1
C
D
  • Question Question
Remove matching rows with pause function
Replies
1
Views
194
alansidman
alansidman
W
  • Question Question
[Excel 365] Maximizing unique combinations of words - No repeats between any two words
Replies
4
Views
421
waffle
W
R
  • Question Question
Smarter alternative to Name Manager & Indirect Formula?
Replies
1
Views
509
RoryA
RoryA

Forum statistics

Threads
1,225,156
Messages
6,183,226
Members
453,152
Latest member
ChrisMd

Share this page

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