Totaling of different currencies in a column separately

[krishco68]

Dear Experts,
I have a range of currencies in a single column, I want to have totals currency wise separately.
the example values are given below. I want the 3 currencies totals separately in another column.

[TABLE="width: 105"]
<colgroup><col></colgroup><tbody>[TR]
[TD] GBP 428.00[/TD]
[/TR]
[TR]
[TD] GBP 264.11[/TD]
[/TR]
[TR]
[TD] GBP 121.22[/TD]
[/TR]
[TR]
[TD] GBP 26.40[/TD]
[/TR]
[TR]
[TD] GBP 10.75[/TD]
[/TR]
[TR]
[TD] GBP 219.53[/TD]
[/TR]
[TR]
[TD] GBP 75.58[/TD]
[/TR]
[TR]
[TD] GBP 10.75[/TD]
[/TR]
[TR]
[TD]₦2,000.00[/TD]
[/TR]
[TR]
[TD]₦1,000.00[/TD]
[/TR]
[TR]
[TD]₦850.00[/TD]
[/TR]
[TR]
[TD]₦550.00[/TD]
[/TR]
[TR]
[TD]₦4,000.00[/TD]
[/TR]
[TR]
[TD]₦4,000.00[/TD]
[/TR]
[TR]
[TD]₹ 7,000.00[/TD]
[/TR]
[TR]
[TD]₹ 2,900.00[/TD]
[/TR]
[TR]
[TD]₹ 600.00[/TD]
[/TR]
[TR]
[TD]₹ 1,000.00[/TD]
[/TR]
[TR]
[TD]₹ 2.88[/TD]
[/TR]
[TR]
[TD]₹ 160.00[/TD]
[/TR]
[TR]
[TD]₹ 150.00[/TD]
[/TR]
[TR]
[TD]₹ 140.00[/TD]
[/TR]
[TR]
[TD]₹ 140.00[/TD]
[/TR]
[TR]
[TD]₹ 1,500.00


[/TD]
[/TR]
</tbody>[/TABLE]

 

[mole999]

you might try three helper cells that each test for the currency symbol

i.e. =IF(Left(cell),4)= "GBP" etc and use a subtotal in the top row

Excel Workbook
	A	B	C	D
1		GBP	?	?
2		1156.34	12400	13592.88
3	GBP 428.00	428
4	GBP 264.11	264.11
5	GBP 121.22	121.22
6	GBP 26.40	26.4
7	GBP 10.75	10.75
8	GBP 219.53	219.53
9	GBP 75.58	75.58
10	GBP 10.75	10.75
11	?2,000.00		2000
12	?1,000.00		1000
13	?850.00		850
14	?550.00		550
15	?4,000.00		4000
16	?4,000.00		4000
17	? 7,000.00			7000
18	? 2,900.00			2900
19	? 600.00			600
20	? 1,000.00			1000
21	? 2.88			2.88
22	? 160.00			160
23	? 150.00			150
24	? 140.00			140
25	? 140.00			140
26	? 1,500.00			1500
Sheet1

 

[sandy666]

if you are able to use PowerQuery try:

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table13"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "name", each Text.Trim([curr],{"0".."9",",","."," "})),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"name", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type"," ","",Replacer.ReplaceText,{"curr"}),
    #"Inserted Kept Characters" = Table.AddColumn(#"Replaced Value", "Kept Characters", each Text.Select([curr], {",", ".", "0".."9"}), type text),
    #"Changed Type1" = Table.TransformColumnTypes(#"Inserted Kept Characters",{{"Kept Characters", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"name"}, {{"Sum", each List.Sum([Kept Characters]), type number}}),
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[name]), "name", "Sum", List.Sum)
in
    #"Pivoted Column"[/SIZE]

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]GBP[/td][td=bgcolor:#70AD47]₦[/td][td=bgcolor:#70AD47]₹[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

1156.34​

[/td][td=bgcolor:#E2EFDA]

12400​

[/td][td=bgcolor:#E2EFDA]

13592.88​

[/td][/tr]
[/table]

 

[krishco68]

Sir,
Where I can put this code, please guide me.
I am not using the pivot table, I am using normal worksheet.
Regards,
Murali Krishna R.

 

[sandy666]

- what is your excel version?
- this is not PivotTable but PowerQuery
- all is in normal worksheet

 

[krishco68]

sir, my excel version is 2010

 

[sandy666]

If you don't have PowerQuery you will need to get free add-in from Microsoft and install proper version (32 or 64 bit)
after that select your range and change it to Excel Table (Ctrl+T)
rename header to curr, PowerQuery is case sensitive so curr and Curr is not the same.
select any cell in your table then find and use From Table
in Power Query Editor use Advanced Editor and replace all code there with code from the post
change the name of table in this line

Source = Excel.CurrentWorkbook(){[Name="[B][COLOR="#FF0000"]Table13[/COLOR][/B]"]}[Content]

to the name of your table
then use Close&Load

 

[krishco68]

Sir,
Is it possible to write a VB code? if so, Please help me in with VB code.

 

[sandy666]

if you want vba you need to wait for someone who will do that for you

have a nice day

 

[Fluff]

Cross posted https://www.excelforum.com/excel-ge...ferent-currencies-in-a-column-separately.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.