List non-matched values in 2 ranges

gtd526

gtd526

Well-known Member
Joined
Jul 30, 2013
Messages
657
Office Version
  1. 2019
Platform
  1. Windows
Hello,
Need to List non-matched value in 2 separate ranges (B & F).
I was able to do them separately, but not together.
How can I List all in a single list?
thank you

The Whole Enchilada.xlsm
ABCDEFGHIJ
38NameSymbol% AssetsNameSymbol% Assets
39Apple IncAAPL10.22%Apple IncAAPL5.72%NVDAGOOG
40Microsoft CorpMSFT9.49%Microsoft CorpMSFT5.27%MRVLBRK.B
41NVIDIA CorpNVDA7.58%Amazon.com IncAMZN3.93%UBERJPM
42Amazon.com IncAMZN6.89%Meta (Facebook)META2.10%NFLXJNJ
43Alphabet Inc Class AGOOGL5.26%Alphabet Inc Class CGOOG1.84%
44Meta (Facebook)META4.02%Alphabet Inc Class AGOOGL1.77%
45Marvell Technology IncMRVL2.69%Tesla IncTSLA1.52%
46Tesla IncTSLA2.10%Bershire HathawayBRK.B1.44%
47Uber Technologies IncUBER1.88%JP MorganJPM1.38%
48Netflix IncNFLX1.82%Johnson & JohnsonJNJ1.28%
401k
Cell Formulas
RangeFormula
I39:I42I39=IFERROR(INDEX($B$39:$B$48,MATCH(1,(COUNTIF($I$38:I38,$B$39:$B$48)=0)*(ISERROR(MATCH($B$39:$B$48,$F$39:$F$48,0))),0)),"")
J39:J42J39=IFERROR(INDEX($F$39:$F$48,MATCH(1,(COUNTIF($J$38:J38,$F$39:$F$48)=0)*(ISERROR(MATCH($F$39:$F$48,$B$39:$B$48,0))),0)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
There might be a way to do this with formulas in Excel 2019 (not my thing) but in the meantime, if you're willing to entertain a UDF option, please try the following. Just select the two ranges (separated by a comma) in the function.

VBA Code: 
Function OnceOnly(rng1 As Range, rng2 As Range) As Variant
    Dim r As Range, a, b, c As Range, i As Long, j As Long, k As Long, m As Long
    Set r = Union(rng1, rng2)
    ReDim a(1 To r.Cells.Count)
    ReDim b(1 To r.Cells.Count)
    i = 1: m = 1
    For Each c In r.Cells
        a(i) = c.Value
        i = i + 1
    Next c
    For i = 1 To UBound(a)
        For j = 1 To UBound(a)
            If a(j) = a(i) Then k = k + 1
        Next j
        If k = 1 Then
            b(m) = a(i)
            m = m + 1
        End If
        k = 0
    Next i
    ReDim Preserve b(1 To m - 1)
    OnceOnly = Application.Transpose(b)
End Function

gtd526.xlsm
ABCDEFGHI
38NameSymbol% AssetsNameSymbol% Assets
39Apple IncAAPL10.22%Apple IncAAPL5.72%NVDA
40Microsoft CorpMSFT9.49%Microsoft CorpMSFT5.27%MRVL
41NVIDIA CorpNVDA7.58%Amazon.com IncAMZN3.93%UBER
42Amazon.com IncAMZN6.89%Meta (Facebook)META2.10%NFLX
43Alphabet Inc Class AGOOGL5.26%Alphabet Inc Class CGOOG1.84%GOOG
44Meta (Facebook)META4.02%Alphabet Inc Class AGOOGL1.77%BRK.B
45Marvell Technology IncMRVL2.69%Tesla IncTSLA1.52%JPM
46Tesla IncTSLA2.10%Bershire HathawayBRK.B1.44%JNJ
47Uber Technologies IncUBER1.88%JP MorganJPM1.38%
48Netflix IncNFLX1.82%Johnson & JohnsonJNJ1.28%
401k
Cell Formulas
RangeFormula
I39:I46I39=OnceOnly(B39:B48,F39:F48)
Dynamic array formulas.
 
Upvote 0
Note that I see you are using Excel 2019. You could simplify the formulas you have now, using the new FILTER function.

So you could this formula in I39:
Rich (BB code): 
=FILTER(B39:B48,COUNTIF(F39:F48,B39:B48)=0)

and this one in J39:
Rich (BB code): 
=FILTER(F39:F48,COUNTIF(B39:B48,F39:F48)=0)

If you had Excel 365, you could take advantage of the new VSTACK function to combine both into one list, i.e
Rich (BB code): 
=VSTACK(FILTER(B39:B48,COUNTIF(F39:F48,B39:B48)=0),FILTER(F39:F48,COUNTIF(B39:B48,F39:F48)=0))
 
Upvote 0
Fluff said:
Unfortunately despite what it says here FILTER function - Microsoft Support the Filter function is only available in 2021 & 365.
Click to expand...
Blasted Microsoft documentation!
That is actually the document I checked before posting this!
Oh well, I guess I just posted a 365 solution!

Note to OP: Some of these new functions, like FILTER, VTACK, LET, TEXTBEFORE, and TEXTAFTER are great and make things much simpler ! I would highly recommend upgrading to 365 if at possible!
 
Upvote 0
First, define the following names (Ribbon >> Formulas tab >> Defined Names group >> Name Manager)...

VBA Code: 
Click New

Name:  IndexNums

Refers to:  =ROW(INDIRECT("1:"&ROWS(Symbols)))

Click Ok

VBA Code: 
Click New

Name:  Symbols

Refers to:  =FILTERXML("<root><item>"&TEXTJOIN("</item><item>",TRUE,'401k'!$B$39:$B$48,'401k'!$F$39:$F$48)&"</item></root>","//item")

Click Ok

Then try the following...

gtd v2.xlsm
ABCDEFGHIJ
38NameSymbol% AssetsNameSymbol% Assets
39Apple IncAAPL0.1022Apple IncAAPL0.05728NVDA
40Microsoft CorpMSFT0.0949Microsoft CorpMSFT0.0527MRVL
41NVIDIA CorpNVDA0.0758Amazon.com IncAMZN0.0393UBER
42Amazon.com IncAMZN0.0689Meta (Facebook)META0.021NFLX
43Alphabet Inc Class AGOOGL0.0526Alphabet Inc Class CGOOG0.0184GOOG
44Meta (Facebook)META0.0402Alphabet Inc Class AGOOGL0.0177BRK.B
45Marvell Technology IncMRVL0.0269Tesla IncTSLA0.0152JPM
46Tesla IncTSLA0.021Bershire HathawayBRK.B0.0144JNJ
47Uber Technologies IncUBER0.0188JP MorganJPM0.0138 
48Netflix IncNFLX0.0182Johnson & JohnsonJNJ0.0128 
401k
Cell Formulas
RangeFormula
I39I39=SUM(IF(FREQUENCY(MATCH(Symbols,Symbols,0),IndexNums)=1,1))
J39:J48J39=IF(ROWS($J$39:J39)<=$I$39,INDEX(Symbols,SMALL(IF(FREQUENCY(MATCH(Symbols,Symbols,0),IndexNums)=1,IndexNums),ROWS($J$39:J39))),"")
Press CTRL+SHIFT+ENTER to enter array formulas.


Hope this helps!
 
Upvote 0
Solution
Domenic said:
First, define the following names (Ribbon >> Formulas tab >> Defined Names group >> Name Manager)...

VBA Code: 
Click New

Name:  IndexNums

Refers to:  =ROW(INDIRECT("1:"&ROWS(Symbols)))

Click Ok

VBA Code: 
Click New

Name:  Symbols

Refers to:  =FILTERXML("<root><item>"&TEXTJOIN("</item><item>",TRUE,'401k'!$B$39:$B$48,'401k'!$F$39:$F$48)&"</item></root>","//item")

Click Ok

Then try the following...

gtd v2.xlsm
ABCDEFGHIJ
38NameSymbol% AssetsNameSymbol% Assets
39Apple IncAAPL0.1022Apple IncAAPL0.05728NVDA
40Microsoft CorpMSFT0.0949Microsoft CorpMSFT0.0527MRVL
41NVIDIA CorpNVDA0.0758Amazon.com IncAMZN0.0393UBER
42Amazon.com IncAMZN0.0689Meta (Facebook)META0.021NFLX
43Alphabet Inc Class AGOOGL0.0526Alphabet Inc Class CGOOG0.0184GOOG
44Meta (Facebook)META0.0402Alphabet Inc Class AGOOGL0.0177BRK.B
45Marvell Technology IncMRVL0.0269Tesla IncTSLA0.0152JPM
46Tesla IncTSLA0.021Bershire HathawayBRK.B0.0144JNJ
47Uber Technologies IncUBER0.0188JP MorganJPM0.0138 
48Netflix IncNFLX0.0182Johnson & JohnsonJNJ0.0128 
401k
Cell Formulas
RangeFormula
I39I39=SUM(IF(FREQUENCY(MATCH(Symbols,Symbols,0),IndexNums)=1,1))
J39:J48J39=IF(ROWS($J$39:J39)<=$I$39,INDEX(Symbols,SMALL(IF(FREQUENCY(MATCH(Symbols,Symbols,0),IndexNums)=1,IndexNums),ROWS($J$39:J39))),"")
Press CTRL+SHIFT+ENTER to enter array formulas.


Hope this helps!
Click to expand...
Thank you!
 
Upvote 0
You're very welcome, and thanks for the feedback.

Cheers!
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
Fetch heads up stats between 2 teams
Replies
2
Views
132
AYouQueTai
A
k3yn0t3
  • Question
Index Match to Grab Values on Another Sheet
Replies
3
Views
314
k3yn0t3
k3yn0t3
E
  • Solved
Get a 2D spilled formula to fulfill a distribution table matching the ranges of both dimensions
Replies
3
Views
71
Fluff
Fluff
U
  • Solved
Formula returning #value, unsure why
Replies
7
Views
323
usnapoleon
U
R
  • Solved
Find multiple matches with wildcard search
Replies
6
Views
201
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,215,219
Messages
6,123,689
Members
449,117
Latest member
Aaagu

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