Can you help me with a formula please

hassanpc

New Member
Joined
Mar 14, 2021
Messages
4
Office Version
  1. 365
Platform
  1. MacOS
i am trying to add vat automatically to my suppliers . i have the same suppliers every months and i need to add vat to them automatically every time i import a statement from the bank into my excel sheet template
and don't want to do that job every week. if cell contains a text (suppliers list) then cell ( amount) * 20% and this should be applied to all suppliers, so if supplier is 0% vat then there will be none added automatically

thank you so much

example of suppliers list i get from bank

1615765193082.png
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

ljubo_gr

Board Regular
Joined
Dec 6, 2014
Messages
239
Office Version
  1. 2016
Platform
  1. Windows
Maybe:
=IF( ISNUMBER (MATCH (BankStatement, NamedRangeOfSuppliers, 0)), Amount*(1+0.2), Amount)
 

ljubo_gr

Board Regular
Joined
Dec 6, 2014
Messages
239
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Hi, Hassan

You need to have a unique list of suppliers(Memo col) as named range. Named Ranges in Excel
Always write formula from the middle, from inside. From middle to the ends.
You wrote that VAT is always 20%, if not, put formula in VAT col similar as in post #2.

Or.....
=IF( ISNUMBER( MATCH( Memo_Col , Supps!$A$1:INDEX(Supps!$A:$A,COUNTA(Supps!$A:$A)) , 0)) , Amount*(1+0.2), Amount)
Where Memo_col are suppliers from bank's statement, Supps is sheet name with all suppliers starting in a1 cell.
Replace these formula names as suits you. Memo is F4, F5...., VAT enter as 0.2 as general formatting and enter in formula where is *(1+G4) part.
As you can, see, i hope, the heart of your formulae is MATCH part. If that MATCH is found then do Amount times 20%, otherwise just Amount.
I hope this helps, i'm excel novice too. :) English also 🤭
 
Last edited:

hassanpc

New Member
Joined
Mar 14, 2021
Messages
4
Office Version
  1. 365
Platform
  1. MacOS
thank you so much for the help
i tried as much as i can , but its not happening with me. sorry if i am troubling you lot with this issue. i included the sheet that i am working on , may be it makes more sense if you see it .

Test Add VAt Automaticcaly to suppliers.xlsx
ABCDEF
1VAT January-2021
2
3DateAmountSuppliersVATGross Inc VAT
4
5 1/4/21($46.78)STRIPE STRIPE-FWHJ8HENG3P DDR
6 1/4/21($20.00)Twitter Online Ads IRELAND
7 1/4/21($9.20)GOOGLE *GSUITE_tax IRELAND
8 1/4/21($476.99)AWS EMEA LUXEMBOURG
9 1/4/21($216.58)FACEBK R4SYXY2ZF2 IRELAND
10 1/4/21($16.00)FACEBK *LUFJQZENV2 IRELAND
11 1/5/21($400.00)207463 33159949 OPTIONAL FT
12 1/5/21($35.20)THOMAS POULSON 74816.131642.FAIL FT
13 1/5/21($20.00)DAVID MARCHAND 75429.SUBS.REF. FT
14 1/5/21($30.00)JOHN WALKER 71544.130623.FAIL FT
15 1/5/21($0.01)DAVID MARCHAND 75429 FT
16 1/5/21($0.01)THOMAS POULSON 74816 FT
17 1/5/21($13.27)STRIPE STRIPE-FWHJ8HENG3P DDR
18 1/6/21($75.82)TWILIO INC USA
19 1/6/21($196.35)STRIPE STRIPE-FWHJ8HENG3P DDR
20 1/6/21($1.20)123-REG WFU8171932 DDR
21 1/7/21($57.68)FRESHWORKS INC USA
22 1/7/21($24.00)Quickbooks ON 06 JAN BDC
23 1/7/21($100.00)H CHALF 74917.APP.SD. STO
24 1/7/21($100.00)PHILLIP FERRINGO 74015.APP.SD STO
25 1/7/21($6.50)********************** COMMISSION FOR
26 1/7/21($55.71)STRIPE STRIPE-FWHJ8HENG3P DDR
27 1/7/21($747.63)TAXIBUTLERSERVICE VDQ88MR DDR
28 1/7/21($500.00)207463 33159949 OPTIONAL FT
29 1/8/21($12.44)STRIPE STRIPE-FWHJ8HENG3P DDR
30 1/11/21($100.00)TAXIAPP HOLDINGS L ACC. OPENING FT
31 1/11/21($2.40)123-REG WFU8171932 DDR
32 1/11/21($19.18)STRIPE STRIPE-FWHJ8HENG3P DDR
33 1/11/21($17.00)FACEBK *GHDR3ZSNV2 IRELAND
34 1/11/21($20.00)LUKE MCCLUSKIE 79750.SUBS.REF. FT
35 1/11/21($0.01)LUKE MCCLUSKIE 79750 FT
36 1/11/21($300.00)SARAH CLEVELAND ADMIN.WE.08.01.21 FT
37 1/12/21($7.99)Microsoft*Microsof ON 12 JAN BDC
38 1/12/21($16.53)STRIPE STRIPE-FWHJ8HENG3P DDR
39 1/13/21($116.47)MailChimp USA
40 1/13/21($20.39)STRIPE STRIPE-FWHJ8HENG3P DDR
41 1/15/21($500.00)GOOGLE ADS11769816 IRELAND
42 1/15/21($1.20)123 REG LTD ON 14 JAN BDC
43 1/15/21($5.99)GOOGLE *Google Pla ON 14 JAN BDC
44 1/15/21($20.84)STRIPE STRIPE-FWHJ8HENG3P DDR
45 1/15/21($360.00)COOPERATIVES UK UCOAA8DBC7-D780 DDR
46 1/18/21($300.00)SARAH CLEVELAND ADMIN.WE.15.01.21 FT
47 1/18/21($34.00)GARY NEAGLE 60505.130866.FAIL FT
48 1/18/21($20.00)DARREN PULESTON 51254.SUBS.REF FT
49 1/18/21($5.00)MOHAMEDALI GULAMO 80316.133277.SCRUB FT
50 1/18/21($1.75)Microsoft US* AUSTRALIA
51 1/18/21($20.63)STRIPE STRIPE-FWHJ8HENG3P DDR
52 1/19/21($10.40)GRANT CABALLERO 80288.134505.FAIL FT
53 1/19/21($25.00)FACEBK *RFVRQYANV2 IRELAND
54 1/19/21($10.00)giffgaff ON 18 JAN BDC
55 1/19/21($195.78)STRIPE STRIPE-FWHJ8HENG3P DDR
56 1/20/21($1.20)123-REG WFU8171932 DDR
57 1/20/21($9.69)STRIPE STRIPE-FWHJ8HENG3P DDR
58 1/21/21($2,000.00)207463 33159949 OPTIONAL FT
59 1/21/21($100.00)H CHALF 74917.APP.SD. STO
60 1/21/21($100.00)PHILLIP FERRINGO 74015.APP.SD. STO
61 1/22/21($300.00)SARAH CLEVELAND ADMIN.WE.22.01.21 FT
62 1/22/21($72.49)STRIPE STRIPE-FWHJ8HENG3P DDR
63 1/25/21$5.07Stripe Payments UK STRIPE BGC
64 1/25/21($20.80)ANTHONY MOORE 67009.133772.FAIL FT
65 1/25/21($89.99)MCAFEE *WWW.MCAFEE IRELAND
66 1/25/21($40.00)JOHN PAUL HARRIS PHONE BURSARY STO
67 1/25/21($8.00)ID MOBILE LIMITED 12086222/001 DDR
68 1/26/21($58.43)VLS RE KLARNA BANK 435597010851624 DDR
69 1/26/21($16.16)STRIPE STRIPE-FWHJ8HENG3P DDR
70 1/27/21($38.00)TESCO STORE 2296 ON 26 JAN CLP
711/27/21($40.00)JAY NICOLA PHONE BURSARY STO
721/27/21($23.40)STRIPE STRIPE-FWHJ8HENG3P DDR
731/28/21($35.20)MOHAMED WARSAME 72836.133320.FAIL FT
741/28/21($100.00)207463 93423441 FT
751/28/21($68.95)GOOGLE *CLOUD_01F2 IRELAND
761/28/21($32.40)STRIPE STRIPE-FWHJ8HENG3P DDR
771/29/21($14.32)STRIPE STRIPE-FWHJ8HENG3P DDR
781/19/21($360.00)GOOGLE IRELAND GG102EWHFZ BGC
791/19/21($300.00)GARY JAMES BROUGHT Gary B FB Ads BGC
801/19/21($34.00)SARAH CLEVELAND ADMIN.WE.26.02.21 FT
811/20/21($20.00)DAVID MINCHIN 79592.TB.SD.02.21 FT
821/20/21($5.00)M J HARRIS STANDDOWN FT
831/21/21($1.75)SARAH CLEVELAND ADMIN.WE.05.03.21 FT
841/21/21($20.63)DAVID FORSTER 70537STANDDOWN FT
851/21/21($10.40)GOOGLE *CLOUD_01F2 IRELAND
861/22/21($25.00)LIQUID 11 LTD ON 28 FEB BDC
871/22/21($10.00)H CHALF 74917.APP.SD. STO
881/25/21($195.78)JAY NICOLA PHONE BURSARY STO
891/25/21($1.20)STRIPE STRIPE-FWHJ8HENG3P DDR
901/25/21($9.69)GOOGLE GSUITE_taxi IRELAND
911/25/21($2,000.00)OURDEBTFREELIVES.C USA
921/25/21($100.00)123-REG WFU8171932 DDR
931/26/21($100.00)STRIPE STRIPE-FWHJ8HENG3P DDR
941/26/21($300.00)STRIPE STRIPE-FWHJ8HENG3P DDR
951/27/21($300.00)ABC ACCOUNTANCY ON 03 MAR BDC
961/27/21($300.00)AWS EMEA LUXEMBOURG
971/27/21($300.00)TAXIBUTLERSERVICE VDQ88MR DDR
981/28/21($300.00)STRIPE STRIPE-FWHJ8HENG3P DDR
991/19/21($300.00)SARAH CLEVELAND ADMIN.WE.05.03.21 FT
1001/19/21($300.00)PAUL GLYNN 67183.SUBS.REF. FT
1011/19/21($300.00)Quickbooks ON 04 MAR BDC
1021/20/21($300.00)STRIPE STRIPE-FWHJ8HENG3P DDR
1031/20/21($300.00)CHARGES COMMISSION FOR
1041/21/21($300.00)FRESHWORKS INC USA
1051/21/21($300.00)PHILLIP FERRINGO 74015.APP.SD STO
1061/21/21($300.00)STRIPE STRIPE-FWHJ8HENG3P DDR
1071/22/21($300.00)STRIPE STRIPE-FWHJ8HENG3P DDR
1081/22/21($300.00)TWILIO INC USA
1091/25/21($300.00)GOOGLE ADS11769816 IRELAND
Monthly Statements try
 

ljubo_gr

Board Regular
Joined
Dec 6, 2014
Messages
239
Office Version
  1. 2016
Platform
  1. Windows
From D column extract unique suppliers with pivot table, then from that table make list of supps. Then use formula in post #5 inside F column.
 

Forum statistics

Threads
1,144,342
Messages
5,723,818
Members
422,518
Latest member
quack_quack

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top