Forum Discussion
Compare unsorted columns without interim calculation
As an elderly Excel tyro, I'm trying to see differences between two unsorted columns.
In the particular case (see dummy example screenshot), I have two aims:
1) In column G, I can show the L1 items in col. B that are missing from L2 items in col D.
But I can't find a way to do this all in col F, without needing an intermediate col F in order to get the result in col G.
I'm sure this must be easy, but can't make it work
2) The next step would be to get the results to display in consecutive rows instead of spaced out as currently in col G. I know I can do this by filtering the results or pasting as data into a separate column and then sorting it, but I'd like to know how to do it straight into my desired column...
- If working in 365:
=LET(b,B3:B15,d,D3:D15,SORT(FILTER(d,ISERROR(MATCH(d,b,0))),1,1))
7 Replies
- Lorenzo : Apologies for not thanking you sooner for your answer. I probably didn't frame my questions clearly, so although it didn't seem to address my supplementary question, (as fixed by Patrick2788), your answer is valuable to me as it shows how to neatly display both sides of the comparison between headings L1 and L2. So I'll have to get familiar with XLOOKUP and XMATCH.
Thanks for taking the time to help.
- If working in 365:
=LET(b,B3:B15,d,D3:D15,SORT(FILTER(d,ISERROR(MATCH(d,b,0))),1,1))- Patrick2788: Apologies for not thanking you sooner -- fixed both my requirements in one go. I can paste it into any old column and it works exactly as I wanted -- although I haven't yet got my head around how it works, I'm sure I'll figure it out, given time.
Many thanks for amazingly quick and effective answer.
=IFERROR(VLOOKUP(D3,B$3:B$15,1,0),D3)Is this what you want to do? Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.
- OliverScheurich: I fear my questions weren't really clearly enough, so I think you've provided a valid answer to a different problem from that which I was clumsily trying to frame.
But I do appreciate the care and work that every answer represents, so many thanks for taking the trouble to answer.
