How to compare different solutions brands and extract specific ones?

Hi to the community,

I'm working on the file "Extraction".

I would like to compare the sheet "Feuil 1" and "Feuil 2". After the comparison, I would like to extract the solutions from the sheet "Feuil 2" that are not established in the sheet "Feuil 1".

In the sheet "Feuil1", the solution's name are located in column A.
In the sheet "Feuil 2", the solution's name are located at the beginning of each sentence, fo example, in the cell G2 "Coupa vs Basware: AP Automation head-to-head technology evaluation and comparison

Thank you a lot for your help and investment for others

Ok AliGW and Glenn Kennedy. I share you the updated workbook with expected results in the file "Extraction v0.2".

A guess,

=LET(
  a,DROP(REDUCE(0,Feuil2!G2:CS10,LAMBDA(x,y,VSTACK(x,IF(ISNUMBER(SEARCH(":",y)),TEXTBEFORE(y,":"),"")))),1),
  b,MAP(a,LAMBDA(x,IF(SUM(--ISNUMBER(SEARCH(Feuil1!A2:A174,x)))>0,"",x))),
  c,UNIQUE(FILTER(b,b<>"")),
  HSTACK(SEQUENCE(ROWS(c)),c)
)

windknife, those ones seems to be missing in your list.

Coupa vs. SAP Ariba
Dhatim Conciliator
Ivalua vs. SAP Ariba
Jaggaer vs SAP Ariba
Oracle Procurement Contracts Cloud
Oracle vs. Coupa
Statess (State of Flux)
Tipalti's Payment Capability

DJunqueira, windknife.

When I use your formulas, they don't work because my solution data doesn't have the right character formalism.

I need to work on the harmonization of the data. For example, SIEVO in the sheet "Feuil 1" need to be Sievo.

I work this on my side and I will apply your formulas after this "cleaning".

Capital letter doesn't seems to be the problem, but misspelling does.

Apexanalytics X APEX Analytix

Another try,

=LET(
 a,DROP(REDUCE(0,Feuil2!G2:CS10,LAMBDA(x,y,VSTACK(x,IF(ISNUMBER(SEARCH(":",y)),TEXTBEFORE(y,":"),"")))),1),
  b,MAP(a,LAMBDA(x,IF(SUM(--(UPPER(Feuil1!A2:A174)=UPPER(x)))>0,"",x))),
  c,UNIQUE(FILTER(b,b<>"")),
  HSTACK(SEQUENCE(ROWS(c)),c)
)

With PQ.
This time I made sure the capital letters were the same.

Formula:

let

    Fonte = Excel.CurrentWorkbook(){[Name="Tabela2"]}[Content],

    #"Tipo Alterado" = Table.TransformColumnTypes(Fonte,{{"Module", type text}, {"Company", type text}, {"Series", type text}, {"Modules", type text}, {"Topics", type text}, {"Results", type text}, {"Result 1 analysed", type text}, {"Result 2 analysed", type text}, {"Result 3 analysed", type text}, {"Result 4 analysed", type text}, {"Result 5 analysed", type text}, {"Result 6 analysed", type text}, {"Result 7 analysed", type text}, {"Result 8 analysed", type text}, {"Result 9 analysed", type text}, {"Result 10 analysed", type text}, {"Result 11 analysed", type text}, {"Result 12 analysed", type text}, {"Result 13 analysed", type text}, {"Result 14 analysed", type text}, {"Result 15 analysed", type text}, {"Result 16 analysed", type text}, {"Result 17 analysed", type text}, {"Result 18 analysed", type text}, {"Result 19 analysed", type text}, {"Result 20 analysed", type text}, {"Result 21 analysed", type text}, {"Result 22 analysed", type text}, {"Result 23 analysed", type text}, {"Result 24 analysed", type text}, {"Result 25 analysed", type text}, {"Result 26 analysed", type text}, {"Result 27 analysed", type text}, {"Result 28 analysed", type text}, {"Result 29 analysed", type text}, {"Result 30 analysed", type text}, {"Result 31 analysed", type text}, {"Result 32 analysed", type text}, {"Result 33 analysed", type text}, {"Result 34 analysed", type text}, {"Result 35 analysed", type text}, {"Result 36 analysed", type text}, {"Result 37 analysed", type text}, {"Result 38 analysed", type text}, {"Result 39 analysed", type text}, {"Result 40 analysed", type text}, {"Result 41 analysed", type text}, {"Result 42 analysed", type text}, {"Result 43 analysed", type text}, {"Result 44 analysed", type text}, {"Result 45 analysed", type text}, {"Result 46 analysed", type text}, {"Result 47 analysed", type text}, {"Result 48 analysed", type text}, {"Result 49 analysed", type text}, {"Result 50 analysed", type text}, {"Result 51 analysed", type text}, {"Result 52 analysed", type text}, {"Result 53 analysed", type text}, {"Result 54 analysed", type text}, {"Result 55 analysed", type text}, {"Result 56 analysed", type text}, {"Result 57 analysed", type text}, {"Result 58 analysed", type text}, {"Result 59 analysed", type text}, {"Result 60 analysed", type text}, {"Result 61 analysed", type text}, {"Result 62 analysed", type text}, {"Result 63 analysed", type text}, {"Result 64 analysed", type text}, {"Result 65 analysed", type text}, {"Result 66 analysed", type text}, {"Result 67 analysed", type text}, {"Result 68 analysed", type text}, {"Result 69 analysed", type text}, {"Result 70 analysed", type text}, {"Result 71 analysed", type text}, {"Result 72 analysed", type text}, {"Result 73 analysed", type text}, {"Result 74 analysed", type text}, {"Result 75 analysed", type text}, {"Result 76 analysed", type text}, {"Result 77 analysed", type text}, {"Result 78 analysed", type text}, {"Result 79 analysed", type text}, {"Result 80 analysed", type text}, {"Result 81 analysed", type text}, {"Result 82 analysed", type text}, {"Result 83 analysed", type text}, {"Result 84 analysed", type text}, {"Result 85 analysed", type text}, {"Result 86 analysed", type text}, {"Result 87 analysed", type text}, {"Result 88 analysed", type text}, {"Result 89 analysed", type text}, {"Result 90 analysed", type text}, {"Result 91 analysed", type text}}),

    #"Colunas Não Dinâmicas" = Table.UnpivotOtherColumns(#"Tipo Alterado", {"Module", "Company", "Series", "Modules", "Topics", "Results"}, "Atributo", "Valor"),

    #"Outras Colunas Removidas" = Table.SelectColumns(#"Colunas Não Dinâmicas",{"Atributo", "Valor"}),

    #"Dividir Coluna por Delimitador" = Table.SplitColumn(#"Outras Colunas Removidas", "Valor", Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.Csv, false), {"Valor.1", "Valor.2"}),

    #"Tipo Alterado1" = Table.TransformColumnTypes(#"Dividir Coluna por Delimitador",{{"Valor.1", type text}, {"Valor.2", type text}}),

    #"Outras Colunas Removidas1" = Table.SelectColumns(#"Tipo Alterado1",{"Valor.1"}),

    #"Duplicatas Removidas" = Table.Distinct(#"Outras Colunas Removidas1"),

    #"Linhas Classificadas" = Table.Sort(#"Duplicatas Removidas",{{"Valor.1", Order.Ascending}}),

    #"Coluna Duplicada" = Table.DuplicateColumn(#"Linhas Classificadas", "Valor.1", "Valor.1 - Copiar"),

    #"Colocar Cada Palavra Em Maiúscula" = Table.TransformColumns(#"Coluna Duplicada",{{"Valor.1 - Copiar", Text.Proper, type text}}),

    #"Consultas Mescladas" = Table.NestedJoin(#"Colocar Cada Palavra Em Maiúscula", {"Valor.1 - Copiar"}, TSolution, {"Nom solution - Copiar"}, "TSolution", JoinKind.LeftOuter),

    #"TSolution Expandido" = Table.ExpandTableColumn(#"Consultas Mescladas", "TSolution", {"Nom solution", "Nom solution - Copiar"}, {"Nom solution", "Nom solution - Copiar"}),

    #"Linhas Filtradas" = Table.SelectRows(#"TSolution Expandido", each ([Nom solution] = null)),

    #"Outras Colunas Removidas2" = Table.SelectColumns(#"Linhas Filtradas",{"Valor.1"}),

    #"Índice Adicionado" = Table.AddIndexColumn(#"Outras Colunas Removidas2", "Índice", 1, 1, Int64.Type),

    #"Colunas Reordenadas" = Table.ReorderColumns(#"Índice Adicionado",{"Índice", "Valor.1"}),

    #"Colunas Renomeadas" = Table.RenameColumns(#"Colunas Reordenadas",{{"Valor.1", "Unique solutions"}})

in

    #"Colunas Renomeadas"

Thank you DJunqueira. It's working. Fun naming for the tab "Feuil PQ" In French, that means toilet paper.

This mess with language is very funny.