• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

how to create a unique reference from tow colunm in diferent sheets

bukaros13

New Member
Hello
I need your help with this problem, I have two sheets with data, each sheet has a column that served me for reference, I need to unify these two columns into one to eliminate the repeated data and copy to a new sheet.
I would like to have a dynamic range formula that reads these columns, which change monthly

I used this formula
=Sheet1!H4:INDEX(sheet1!H4:H1048576,COUNTA(sheet1!H:H)) but i can't add the other data

Thank you very much in advance
 

Attachments

  • Chandoo-Help.xlsx
    45.5 KB · Views: 6
Last edited:
this formula works, but excel gets very very slow, it is almost impossible the update the data

=IFERROR(IFERROR(INDEX(List1,MATCH(0,IF(ISBLANK(List1),1,COUNTIF($B$10:B1001,List1)),0)),INDEX(List2,MATCH(0,IF(ISBLANK(List2),1,COUNTIF($B$10:B1001,List2)),0))),"")

Please some help

thanks
 
This uses Excel 365 Insider. It starts by appending one dataset to the other and then uses the new dynamic array functions UNIQUE and SORT.
Code:
= LET(
  m, ROWS(List1),
  n, ROWS(List2),
  k, SEQUENCE(ROWS(List1) + ROWS(List2)),
  combined, IF( k<=m, INDEX(List1, k), INDEX(List2, k-m) ),
  distinct, UNIQUE(combined),
  SORT(distinct) )
There are similar approaches with the SMALL function used to consolidate lists with duplicates blanked out.

Another approach is to use PowerQuery to append the lists and then removing duplicates and sorting are mainstream processes.
Code:
let
    Source = Table.Combine({ListA, ListB}),
    #"Removed Duplicates" = Table.Distinct(Source),
    #"Sorted Rows" = Table.Sort(#"Removed Duplicates",{{"LOOK UP VALUE", Order.Ascending}})
in
    #"Sorted Rows"
That has the advantage as being more widely available. Neither approach appears to have speed or efficiency issues.
 
Back
Top