Convert Months Spanish Language to Portuguese and do Lookups + Sum?

Lacan

Lacan

Board Regular
Joined
Oct 5, 2016
Messages
237
Office Version
  1. 365
Platform
  1. Windows
Hi Guys,👌

Would like help with this case, have almost 50.000 rows of data in which the originally months are in Spanish Language however my Office Version is in Portuguese.
Accordingly with table example below have to do some lookups with sum months.
Would like to make this lookups sums months in Portuguese.
Hope I was clear.
By the way dont have translator function in my Excel Version.
All the help is welcome!
Thank you very much.🙏👍


Fórmula Principal para o Ficheiro VDF.xlsm
ABCDEFGHIJKLM
1
2CUSTOMERMONTHSALESCUSTOMERANNASpanish MonthsPortuguese Months
3ANNAsep. 20245DAVIDDAVIDene. 2024ene. 2024
4ANNAoct. 202410feb. 2024feb. 2024
5DAVIDmay. 20242Spanish Monthsmar. 2024abr. 2024may. 2024mar. 2024mar. 2024
6DAVIDfeb. 202434992abr. 2024abr. 2024
7ANNAabr. 20244may. 2024mai. 2024
8DAVIDjul. 20243Portuguese Monthsmar. 2024abr. 2024mai. 2024jun. 2024jun. 2024
9ANNAoct. 20245jul. 2024jul. 2024
10ANNAene. 20245ago. 2024ago. 2024
11DAVIDabr. 202499sep. 2024sep. 2024
12ANNAmay. 202410oct. 2024out. 2024
13ANNAjun. 20243nov. 2024nov. 2024
14ANNAjul. 20241dic. 2024dez. 2024
15DAVIDsep. 20247
16ANNAoct. 20249
17ANNAnov. 20243
18ANNAdic. 20244
19ANNAfeb. 20242
20DAVIDmar. 20244
21DAVIDago. 20243
22ANNAmar. 202450
23
Folha8
Cell Formulas
RangeFormula
H2:H3H2=UNIQUE(Tabela5[CUSTOMER])
G6:I6G6=SUMPRODUCT(IFERROR((Tabela5[CUSTOMER]=$G$3)*(Tabela5[MONTH]=G5)*(Tabela5[SALES]),0))
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
G3List=$H$2#
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
If you have proper date values you can use the text function to display it any way you want in any language.
below is an example:
Book1
IJKL
29English UKSpanish SpainPortuguese Portugal
30[$-en-GB]dd mmmm yyyy;@[$-es-ES]d-mmm-yy;@[$-pt-PT]mmm. yyyy;@
3101.01.202401 January 20241-ene.-24jan. 2024
3201.02.202401 February 20241-feb.-24fev. 2024
3301.03.202401 March 20241-mar.-24mar. 2024
3401.04.202401 April 20241-abr.-24abr. 2024
3501.05.202401 May 20241-may.-24mai. 2024
3601.06.202401 June 20241-jun.-24jun. 2024
3701.07.202401 July 20241-jul.-24jul. 2024
3801.08.202401 August 20241-ago.-24ago. 2024
3901.09.202401 September 20241-sep.-24set. 2024
4001.10.202401 October 20241-oct.-24out. 2024
4101.11.202401 November 20241-nov.-24nov. 2024
4201.12.202401 December 20241-dic.-24dez. 2024
Sheet1
Cell Formulas
RangeFormula
J31:L42J31=TEXT($I31,J$30)
I32:I42I32=EOMONTH(I31,0)+1
 
Upvote 0
Solution
bobsan42 said:
If you have proper date values you can use the text function to display it any way you want in any language.
below is an example:
Book1
IJKL
29English UKSpanish SpainPortuguese Portugal
30[$-en-GB]dd mmmm yyyy;@[$-es-ES]d-mmm-yy;@[$-pt-PT]mmm. yyyy;@
3101.01.202401 January 20241-ene.-24jan. 2024
3201.02.202401 February 20241-feb.-24fev. 2024
3301.03.202401 March 20241-mar.-24mar. 2024
3401.04.202401 April 20241-abr.-24abr. 2024
3501.05.202401 May 20241-may.-24mai. 2024
3601.06.202401 June 20241-jun.-24jun. 2024
3701.07.202401 July 20241-jul.-24jul. 2024
3801.08.202401 August 20241-ago.-24ago. 2024
3901.09.202401 September 20241-sep.-24set. 2024
4001.10.202401 October 20241-oct.-24out. 2024
4101.11.202401 November 20241-nov.-24nov. 2024
4201.12.202401 December 20241-dic.-24dez. 2024
Sheet1
Cell Formulas
RangeFormula
J31:L42J31=TEXT($I31,J$30)
I32:I42I32=EOMONTH(I31,0)+1
Click to expand...

Dear @bobsan42

In one word - PERFECT!!!! 🔝🔝👍👍🙏🍻🍻
Thank you very much!!!
 
Upvote 0
You must log in or register to reply here.

Similar threads

MrDB4Excel
  • Question Question
Lookup Amount Varies For Specific Category
Replies
4
Views
561
Alex Blakenburg
A
C
  • Solved
Sumproduct with multiple rows and lookup second array column criteria
2
Replies
12
Views
4K
KRice
K
A
  • Solved
Soma mensal acumulada / Accumulated Monthly Sum
Replies
2
Views
614
asampaio
A
R
  • Question Question
Sum values that match in different rows
Replies
0
Views
330
rlobera
R
L
  • Locked
  • Question Question
No good with Access
Replies
1
Views
596
Joe4
Joe4

Forum statistics

Threads
1,224,820
Messages
6,181,155
Members
453,021
Latest member
Justyna P

Share this page

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
Back
Top