Need help to search Purchase number for sheet1 to Merged column in different sheet

[KuntalSingh]

I have a column in sheet 1 contains list of Purchase number I need to searched these Purchased from another sheet have coulumn Merged. Also after searched need to segrate data based on Specail G/L from column Special G/L ind. PFB the sample data.

List of GL

Special G/L ind.

6

7

8

C

A

D

E

Sheet 2

Supplier	Posting Date	Special G/L ind.	Amount in local currency	Document Date	Clearing Document	User Name	Local Currency	Net due date	Entry Date	Time of Entry	Merged
10109896	########	E	-667.37	########		00033174	INR	########	########	########	0025933159/00000 8350049836 SD VIM Internet Pmnt-0010109896
11935132	########	E	-7405.18	########		00083214	INR	########	########	########	0026202941/00000 4300047702 SD RECOVERED AS BG EXPIRED REC/DR ADJ VIM Recovery/Debit Adjusted
10211292	########	8	91544.48	########		B2BPRD	INR	########	########	########	0028562105 4300009482 354541434/31.07.2023 2859060202314520 72859060202314520 28562105
10103391	########	7	11746794	########		WF-BATCH	INR	########	########	########	4300009543 CLAIMING -5% ADVANCE AGAINST APPROVAL OF DRAWINGS( 1809/001/2023-24 1809/001/2023-24 28490653
10103391	########	7	2.02E+08	########		00083216	INR	########	########	########	28468654 1100035326 70% of LSC 328 Inv P/23-24/54 P/23-24/54/ 28468654
11999530	########	E	-29066	########		00017663	INR	########	########	########	26772088 7200008467 2020_WRO_127108_1- ISD-PMCC Contract KKBK0000958*KKBK KKBK0000958*KKBKH21015619
10254280	########	E	-4380	########		00040389	INR	########	########	########	0028751674/00000 8350129785 DEDUCTION @ 3% SD VIM Internet Pmnt-0010254280
11932117	########	)	-2E+07	########		00501760	INR	########	########	########	PRICE DISCOUNT 4300000118 PRICE DISCOUNT_10%_FINAL BILL_ 23329315 FINAL BILL FINAL BILL_23329315 23329315
11935850	########	*	-58760	########		FI_BKG	INR	########	########	########	0704159492740 7800000145 NA E-Pay. Portal
13281937	########	C	-1000000	########		00010877	INR	########	########	########	00512296 1000549 6496 - LPG MATERIALS /SEDE 30680IGL0000120
11037832	########	C	-750000	########		00019968	INR	########	########	########	00510371 1000481 LPG/BULK/TT/NR /SEDE 007BG01191150002
11935132	########	C	-3182370	########		00513593	INR	########	########	########	00028001 1000031 26044826 - RCC/NR/LPG-S&D/PT-172/2017-18 /SEDE INBG03619000062
11935850	########	E	-13707.1	########		00040389	INR	########	########	########	0028869897/00000 8350004834 SD @ 3% VIM Internet Pmnt-0011935850
11935850	########	E	-27920	########		00083152	INR	########	########	########	0028949843/00000 8350006581 SD@10% VIM Internet Pmnt-0011935850
11935850	########	*	-23729	########		00040389	INR	########	########	########	0029073480/00010 8350010019 SD DED 23729/- 10% VIM Internet Pmnt-0011935850
11935850	########	*	-8047	########		00083152	INR	########	########	########	0029091642/00010 8350010114 8047/- SD DED 10%@ VIM Internet Pmnt-0011935850
11935850	########	E	-47236.6	########		00040389	INR	########	########	########	0029201439/00000 8350013851 29201439 SD@10% OF RA VIM Internet Pmnt-0011935850
11935850	########	*	-8739.5	########		00040389	INR	########	########	########	0029262641/00000 8350014889 8739.45/- SD DED 10% VIM Internet Pmnt-0011935850
11935850	########	)	-467	########		00083152	INR	########	########	########	0028283548/00000 8350016466 PRD @ 0.5% VIM Internet Pmnt-0011935850
11935850	########	E	-6793.62	########		00083152	INR	########	########	########	0028400580/00000 8350019064 SD @3% VIM Internet Pmnt-0011935850
11979984	########	E	-6325.29	########		00033174	INR	########	########	########	0028939747/00000 8350006069 SECURITY DEPOSIT VIM Internet Pmnt-0011979984
10105480	########	C	-293490	########		00059725	INR	########	########	########	00082651 1000809 11-27536705-3% SD/BG 0027536705/SEDE 1244IGPER000322

Sheet1

Purchasing Document
28444307
28650051
28515976
28643689
28641645
28524383
29287171
28706252
28611478
29119447
29272001
29265306
29279658
29271331
29180722
28701549
29176737
29259107
29265497
27501205
29035680
29269861
29269925
29270011
29269777
29270793
27041047
26376304
29176599
28826984
28754460
28427167
29056728
28865754
28926286
28624910
27484022
29267391
26592165
25933159
29211489
29268136
29083423
28689919
29239006
28491764
28684851
29128178
29070748
28323730
29218519
26752988
29153224
28962124
28924108
29244899
26405854
26403743
26403360
26405490
29168639
29204475
29237222
29192292
28626893
29147722
28761834
29258047
29258011
28751674
29260003
29109240
29254967
29254517
29212591
29212537
27661282
28328227
28768026
29241635
29253793
28800627
27536705
28732144
29233654
26772088
28872261
29198141
29314488
28582717
28424750
29069301
28968938
26202941
28899428
28987259
28858321
29062648
28669909

[lbendlin]

Please provide a more detailed explanation of what you are aiming to achieve. What have you tried and where are you stuck?

[KuntalSingh]

I need to search PO number in column Merged then basis of search result need to segregate the data into different sheet based on GL

[lbendlin]

only a very small subset of your sample PO numbers match

Sheet1:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TVTJdSRRDMrF5zloX2Lxc/5pDP1conykC+kDkvr7+8smIlz66+ffB1SKpD4gNbfrvoTXLIFW5NEsfPwXrE1rX4OWsrSrUY2eh6a6EX01bSJ6oNKl+GUrh0Ddj6YjbcZ3NGPvS1c7W+eq9Ns69gGdoiaPhRXPGiFtIfYFaEIF0PnnS/drQXqfDFpC5cyVd7nEqy33QpzPQ3EWMqIeBROwWtdastrmairBfAAkINaL1wJWHwUxIZcOWO37qMbzpvX4yUWeebkZ5jMENeo3BRkP83tndpU0X7lhTXzsBWkxyWHZKEcviCrOj5u3M3hFydO6Om2H65JuRttl+gJD2NyQgINrEJJzUQF4h7/AL+sPLfYUaE05zQlWlKN3rNslqmv4hcEjklt/rPiftSxFctzEkXfjAZRXkhg2aSUi7CawxHSgs94GkUqgnzX/A279uwrRG7Mes6a2EeOZ4ZydTlF+fmYwXuPJePWdzLSbBlUjUGaNBGS4sIiDx6Q7Ggcc5aTlYOVPWxhyP9tS6/fngNEj6qemTGyv22DuwStZvJo8s4RzZTdM/GiF2xbQfv4D", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Purchasing Document" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Match", (k)=> Table.SelectRows(Sheet2, each Text.Contains([Merged],k[Purchasing Document]))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each Table.RowCount([Match])),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Custom] = 1)),
    #"Expanded Match" = Table.ExpandTableColumn(#"Filtered Rows", "Match", {"Special G/L ind.", "Merged"}, {"Special G/L ind.", "Merged"})
in
    #"Expanded Match"