Search code examples
excelexcel-formula

How to match large data on excel based on some criteria?

I am using MS Office Pro Plus 2019. I have an excel that has 6 columns inside.

A = ARMADAW, B = ARMADAS, D = SAPIWC, E = ARMADAIWC, G = SAPW, H = SAPS

enter image description here

I would like to color the matching values B and H. But there are criterias.

  • For ARMADAS B2: D542295890, I am getting the ARMADAW A2:B05KAYIP
  • I will look for B05KAYIP in the E column ARMADAIWC and get the corresponding D SAPIWC value 0010
  • Then I look for 0010 in column G SAPW and get the matching value of D542295890 ARMADAS B2.
  • If B and H matches then I want to color them.

I wrote a console application with C# but since there are approx. 395000 rows it takes too much to complete. In fact it is still trying to match the values right now :)

Is there a faster way with Excel to do this? I need help here.

Here is sample data:

ARMADAW ARMADAS     SAPIWC  ARMADAIWC       SAPW    SAPS
B05KAYIP    D542295890      0001    TT Mobil_SITES      34CS    210305601410F7000079
B06KAYIP    99121839        0010    Consumer Str.Loc        0030    210305601410F7000083
B05KAYIP    210212D4726TB1004467        0011    E-SIM Brysl Digi        34CS    210305601410F7000078
B05KAYIP    21013158489TAB002570        0012    E-SIM M2M Digitl        34CS    210305601410F7000077
B06KAYIP    21021205889TAB002094        0030    B08KAYIP        0055    210305601410F7000076
B08KAYIP    CM90012939      0040    Public Stor.Loc.        0055    210305601410F7000075
B05KAYIP    FP110305448     0050    Other groups StL        34CS    210305601410F7000074
B08KAYIP    FP110305447     0055    B06KAYIP        34CS    I0I0728878
B05KAYIP    I0E1717261      0060    Corporate Str.L.        0055    99121839
B011KAYIP   00004240        0065    Scrap Str.L.        34CS    I0I0728685
B05KAYIP    05004326        0080    Buffer Stock        0030    FP110305447
B013KAYIP   05004336        34CS    Macka Stor.Loc.     34CS    I0I0728756
B05KAYIP    05004345        0100    G-ADN       34CS    I0I0728837
Macka Stor.Loc. I0I0728837      0101    ADANA_OMC       34CS    I0I0728797

Here is the error message when I use this formula:

=EĞERHATA(KAYDIR(İNDİS(H$2:H$4,KAÇINCI(B2,H$2:H$4,0)),,-1)=İNDİS(D$2:D$4,KAÇINCI(A2,E$2:E$4,0)),YANLIŞ)

enter image description here

Solution

  • This formula in a cell (e.g. J2 and drag down) looks for the appropriate values in the two tables, and if a match is found return TRUE otherwise FALSE

    =IFERROR(OFFSET(INDEX(H$2:H$4,MATCH(B2,H$2:H$4,0)),,-1)=INDEX(D$2:D$4,MATCH(A2,E$2:E$4,0)),FALSE)

    The result of the formula on sample data:

    enter image description here

    This is Markdown format with site https://tabletomarkdown.com/convert-spreadsheet-to-markdown/

    ARMADAW ARMADAS SAPIWC ARMADAIWC SAPW SAPS
    B05KAYIP D542295890 0001 TT Mobil_SITES 34CS 210305601410F7000079
    B06KAYIP 99121839 0010 Consumer Str.Loc 0030 210305601410F7000083
    B05KAYIP 210212D4726TB1004467 0011 E-SIM Brysl Digi 34CS 210305601410F7000078
    B05KAYIP 21013158489TAB002570 0012 E-SIM M2M Digitl 34CS 210305601410F7000077
    B06KAYIP 21021205889TAB002094 0030 B08KAYIP 0055 210305601410F7000076
    B08KAYIP CM90012939 0040 Public Stor.Loc 0055 210305601410F7000075
    B05KAYIP FP110305448 0050 Other groups StL 34CS 210305601410F7000074
    B08KAYIP FP110305447 0055 B06KAYIP 34CS I0I0728878
    B05KAYIP I0E1717261 0060 Corporate Str.L. 0055 99121839
    B011KAYIP 00004240 0065 Scrap Str.L. 34CS I0I0728685
    B05KAYIP 05004326 0080 Buffer Stock 0030 FP110305447
    B013KAYIP 05004336 34CS Macka Stor.Loc. 34CS I0I0728756
    B05KAYIP 05004345 0100 G-ADN 34CS I0I0728837
    Macka Stor.Loc. I0I0728837 0101 ADANA_OMC 34CS I0I0728797