VBA Count Totals and paste relevant number

Mirinjawbro · Jan 29th 2021

Hello

each week a spreadsheet of numbers is uploaded. the list length varies but the terms and conditions do not. (red at the top)

i have made the macro copy column a2 down to the end and paste into a seperate tab, then go to b2 and then paste it underneath and so on. thats working and fine

what i now need is too add 1+23 from A1 the correct number of times to the right of A2 downwards.

then take 1+23 in B1 and add it to the correct number of lines in B2 downwards

so , lets assume this has 100 rows

column A needs to be A2 down to A101 and next to this 1+23 100 times

if the list was 87 long, id need A2-88 and 1+23 next to it 87 times

then it goes to the next coulmn and repeats

thanks

1+23	1+23	1+23	1+23	1+23
8000	10000	12000	15000	20000
20.00	21.19	23.76	28.65	42.48
20.00	20.00	22.11	25.52	32.35
20.00	21.19	23.76	28.65	42.48
20.00	20.00	22.11	25.52	32.35
20.00	20.00	22.11	25.52	32.35
20.00	22.39	25.21	30.37	44.25
28.22	29.76	31.30	47.61	68.58
20.00	22.97	26.94	32.05	38.95
31.07	32.96	34.50	52.96	74.98
31.07	32.96	34.50	52.96	74.98
20.00	25.15	29.55	35.15	42.15
31.07	32.96	34.50	52.96	74.98
20.00	25.15	29.55	35.15	42.15

**alansidman** · Jan 29th 2021

I may be a bit thick on this so would you take your example above and mock up what your solution should look like. You may want to do it in a spreadsheet and attach it to the thread.

Mirinjawbro · Jan 30th 2021

	1	2	3		OUTPUT
	8000	10000	15000		Name	Term	Group	Cat
aaa	a	i	f		aaa	1	8000	a
bbb	b	g	d		bbb	1	8000	b
ccc	c	d	r		ccc	1	8000	c
ddd	d	f	t		ddd	1	8000	d
eee	e	h	y		eee	1	8000	e
fff	f	y	e		fff	1	8000	f
ggg	g	f	d		ggg	1	8000	g
hhh	h	d	f		hhh	1	8000	h
					aaa	2	10000	i
					bbb	2	10000	g
					ccc	2	10000	d
					ddd	2	10000	f
					eee	2	10000	h
					fff	2	10000	y
					ggg	2	10000	f
					hhh	2	10000	d
					aaa	3	15000	f
			bbb	3	15000	d
			ccc	3	15000	r
			ddd	3	15000	t
			eee	3	15000	y
			fff	3	15000	e
			ggg	3	15000	d
			hhh	3	15000	f

Mirinjawbro · Jan 30th 2021

this example there are 8 lines so it pastes the group and term 8 times with the relevant data

there could be 3000 lines, so it would need to count to 3000 and paste the group and term 3000 times with the data

hopefully ive explained better

**Carim** · Jan 30th 2021

Hello,

What Alan was suggesting ... is the following :

He is willing to give a hand ... provided you do attach your Excel file with your next message

i.e. A sample file with enough records to test ... the same structure as your real life file ... no sensitive data ...

**alansidman** · Feb 1st 2021

I would do it with Power Query. Here is the Mcode and a sample file based upon what you provided.

Code

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Demoted Headers" = Table.DemoteHeaders(Source),
#"Transposed Table" = Table.Transpose(#"Demoted Headers"),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Transposed Table", {{"Column2", type text}}, "en-US"),{"Column1", "Column2"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"),
#"Transposed Table1" = Table.Transpose(#"Merged Columns"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Column1:"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
#"Sorted Rows" = Table.Sort(#"Split Column by Delimiter",{{"Attribute.1", Order.Ascending}, {"Column1:", Order.Ascending}})
in
#"Sorted Rows"

Display More

Mirinjawbro · Feb 1st 2021

thankyou very much, will give it a go.

Mirinjawbro · Feb 1st 2021

attached example

the bit im struggling with is to get the number of lines it has in total and then paste the relevant headers that many times

hopefully the attachment is better

thanks

**alansidman** · Feb 1st 2021

Code

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Transposed Table" = Table.Transpose(Source),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Transposed Table", {{"Column1", type text}, {"Column2", type text}}, "en-US"),{"Column1", "Column2"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"),
#"Transposed Table1" = Table.Transpose(#"Merged Columns"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"SPECIFICATION        :"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
#"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter",{{"Attribute.1", "Term"}, {"Attribute.2", "Miles"}, {"Value", "Cost"}}),
#"Sorted Rows" = Table.Sort(#"Renamed Columns",{{"Term", Order.Ascending}})
in
#"Sorted Rows"

Display More

Mirinjawbro · Feb 1st 2021

thanks alot for that

never used power query so will try and work out from the code above the steps you took.

thanks

**alansidman** · Feb 2nd 2021

A good primer to help you is "M is for (Data) Monkey" by Ken Puls and Miguel Escobar. It can be found on Amazon.

VBA Count Totals and paste relevant number

Participate now!

Share