Search results

i got it to work except for approximate match =HLOOKUP(B2,'source-data'!$G$2:$J$1000,1,0)

Thanks to @Marc L , i just made a slight modification for colour =6. I am indeed grateful

yes, i did, i tried it out and even sent you a screenshot of how i would circumvent, as colour was reflected in all cells, none excepting. see #4 I even carefully saved the file, as its bound to save me hours and hours of work thru visual checking, which may not be possible with larger files.

i tried a VBA solution via copilot, am not sure if its a good idea to post it here, maybe i breach forum rules, let me know, anyway i got the opposite effect !!

office 365, the other data was retrieved using FILTER, observed your worksheet, on issuance dates its highlighting all cells, instead of leaving them void of colour. Maybe i can filter them out like this, its our person who issued certificate from main portal in feb 2024 but we cant see...

i want to highlight values in column I in yellow along with values in column Q,R,S,T,U if they fall within the same month. Power query, conditional formatting solutions are ok too. sample worksheet is attached too.

right, there is also an issue with may 2023 as its a table that refuses to be deleted, it was fine last month. sorry for sending pic, the xlsx file is like 194 MB, but you can discern.

it solved the 2nd part but in spite of several attempts, for the first part couldn't do it even format was general for all columns, i may go power query way as my learning curve picks.

Once again , my sincere apologies for not posting a very large 200mb xlsx file, but i suppose you would only look at the formula and discern, every month i delete a sheet for prior month and add a sheet for the new month, and update formula correspondingly. however this month, am getting a...

How about using fuzzy look up feature from microsoft ?? Fuzzy lookup

in india mean 100,000 means lakh or lac, they are different as they 1,00,000

yes, it works fine, i tried it out

exactly, the july are not being deleted. i checked it out. the rule of thumb is keep drn and rec for aug same policy , the july (drn) and aug (rec) same policy, but delete rest. MARTHA MAKWATA MARTIS INSURANCE AGENCY 140/084/1/027307/2023/ 27-Jul DRN 6899 MARTHA MAKWATA MARTIS INSURANCE...

I mean @Marc L that's just excellent, it works like a charm, now the only thing will be to test it on large file and hope it does not take up much time. I was just about to cross post to the other forum when your reply came thru. At least you got the part of policy no. to be both on DRN and REC.

Those you shaded in YELLOW above are to be deleted, Rule of thumb: if policy no. is for DRN and REC is same and for aug and aug, keep, if policy no. is same for DRN is july and REC is aug, keep. Rest delete any with policy no is same but has DRN,REC and CRN (to delete). If policy no. is only one...

Exactly am getting same error, then i start deleting duplicated posts like now even.... and when i will post reply it will oops me again....

You're not the only one, I'm getting errors like Oops post not sent too, so i end up duplicating posts as well, but after review i delete those, for sake of clarity and neatness.

would this table help ??, as i had removed totals, the difficulty herein lies when there is july and aug debit and receipt together. focal point is policy nos Unit manager Agency policy nos(unique) Date Type amount Action needed on row EVANS smithson SUN-Asean INSURANCE AGENCY...

upload an xlsx file for better perception.

DRN means debit, REC means receipt, CRN means credit note. All with one policy no. need to be deleted whether in july or aug If there is DRN in july and REC in Aug, we need that info, same policy no. If there is DRN in aug and REC in Aug, we need that info. Rest to be deleted, Apologies if i...

Ok, i get you now, what you mean is, i am using plus in arithmetic way +, No, i am using it as a conjunction as below:= "And also; in addition to" not as an arithmetic sign.

Umm..... Let me just show the worksheet before and after, showing desired results, Thanks. see below is the results i desire, rest are to be omitted, rule of thumb: only aug transactions debit rec, July aug deb receipt , rest like crn ,single debit, single receipt, single crn in July and aug not...

Yes those you mentioned are rows to keep or retain. July and aug debit plus receipt, aug debit plus receipt, rest are not relevant and are desired to be omit from xlsx file. Also anything which is solo, ie the receipt has no corresponding debit, only one policy no. is said to be of no...

Yes those you mentioned are rows to keep or retain. July and aug debit plus receipt, aug debit plus receipt, rest are not relevant and are desired to be omit from xlsx file.