Forum Discussion

JCBenny's avatar
JCBenny
Copper Contributor
Jun 13, 2023

Double Xlookup not working for what I need

So I am trying to pull information from two charts that have a matching identifying value, however the information in the columns following that unique value can be different and these values can rep...
excel
Formulas and Functions
JCBenny's avatar
JCBenny
Copper Contributor
Jun 15, 2023

mtarler 

 

I really appreciate you looking into this for me. You are right, I was rushing to explain this and did a pretty bad job so let me simplify what I really need.

 

Below is an example of the data I'm using. I work in the medical industry and have thousands of these rows for just one project.

 

As you can see, something like "ANA-C069B" is used multiple times but in different ways. I need to pull each distinct/unique way it is listed, row by row, without pulling any duplicates. I need to do that with every one of the IDs not just "ANA-CO69B" throughout my thousands of rows. That is my main problem here. 

 

Any help is greatly appreciated!

 

ANA-C069B06 - Intermediate Care / ICUSoiled WorkroomAnalyzer, Lab: Urinalysis, Semi-AutomatedSiemens Healthcare DiagnosticsClinitek Status +$1,200.00
ANA-C069B06 - Intermediate Care / ICUSoiled WorkroomAnalyzer, Lab: Urinalysis, Semi-AutomatedSiemens Healthcare DiagnosticsClinitek Status +$1,200.00
ANA-C069B07 - Med/SurgSoiled WorkroomAnalyzer, Lab: Urinalysis, Semi-AutomatedSiemens Healthcare DiagnosticsClinitek Status +$1,200.00
ANA-C069B07 - Med/SurgSoiled WorkroomAnalyzer, Lab: Urinalysis, Semi-AutomatedSiemens Healthcare DiagnosticsClinitek Status +$1,200.00
ANA-D36C802 - LabChemistryAnalyzer, Lab: ChemistryRoche Diagnostics CorporationCobas Pure C 303 Analytical Unit$38,870.00
ANA-D39A002 - LabCoagulationAnalyzer, Lab: Coagulation, PlasmaWerfenACL TOP 350 CTS$105,000.00
ANA-D60FC02 - PACU (12 Bay)Nurse Station (with Nourishment & Team Work)Analyzer, Lab: Glucose, Point-of-CareNova BiomedicalNova StatStrip Glucose Hospital Meter w/1D Barc$700.00
ANA-D60FC02 - Pre-Op (Day Surgery)Nurse/Central StationAnalyzer, Lab: Glucose, Point-of-CareNova BiomedicalNova StatStrip Glucose Hospital Meter w/1D Barc$700.00
ANA-D60FC02 - Pre-Op (Day Surgery)Nurse/Central StationAnalyzer, Lab: Glucose, Point-of-CareNova BiomedicalNova StatStrip Glucose Hospital Meter w/1D Barc$700.00
ANA-D60FC02 - Pre-Op (Day Surgery)Nurse/Central StationAnalyzer, Lab: Glucose, Point-of-CareNova BiomedicalNova StatStrip Glucose Hospital Meter w/1D Barc$700.00
ANA-D60FC03 - Ante/Post PartumMeds (Post Partum)Analyzer, Lab: Glucose, Point-of-CareNova BiomedicalNova StatStrip Glucose Hospital Meter w/1D Barc$700.00
mtarler's avatar
mtarler
Silver Contributor
Jun 15, 2023

JCBenny  Very simply =UNIQUE(A1:G10000) should do that for you.  You can also use DATA -> Remove Duplicates if you just want to remove those duplicate rows

  • JCBenny's avatar
    JCBenny
    Copper Contributor
    Jun 15, 2023
    Okay great! Thanks!

    Now the hard part for me. I have two arrays. Arrays 1 & 2 have similar formulated data. However, array 2 has three times as much data. I need to know how much similar data both arrays have (row for row). I need to create a third array made up of all the rows that both arrays 1 & 2 have in common. The rows have to match exactly, not just the first column of identifiers since columns 2 and 3 can change.

    That is where I have been failing. I can't find a way to insert the unique function into a Index-Match or Xlookup function.
    • mtarler's avatar
      mtarler
      Silver Contributor
      Jun 15, 2023

      JCBenny Try this

      =LET(UniqSets,VSTACK(UNIQUE(Table1),UNIQUE(Table2)),
      AllUniq, UNIQUE(UniqSets),
   NonOverlap, UNIQUE(UniqSets,,TRUE),
          out,IFERROR(UNIQUE(VSTACK(AllUniq,NonOverlap),,TRUE),"none"),
      out)

       

      • JCBenny's avatar
        JCBenny
        Copper Contributor
        Jun 17, 2023

        mtarler It returned "none". I triple checked to make sure I had I did it right.

Resources