Having trouble copy pasting specific text into new column/row

[jimsjoh]

Hello, trying to automate a big spreadsheet with 1500+ rows and not having much luck when trying VBA myself.

I have 3 columns.
Column A which contain cells with "Sum of" followed by a locale code such as en_US, the locale code changes.
Following each "Sum of" cell there is a number of project rows also in Column A, amount varies.
Column B with some values that is not important for this.
Column C I want to paste the locale code for each row of the project rows.
Loop it until there are no more rows with "Sum of" cells left.

Example:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Sum of en_US[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Task1[/TD]
[TD]123[/TD]
[TD]need "en_US" here[/TD]
[/TR]
[TR]
[TD]Task2[/TD]
[TD]321[/TD]
[TD]need "en_US" here[/TD]
[/TR]
[TR]
[TD]Sum of en_GB[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Task1[/TD]
[TD]345[/TD]
[TD]need "en_GB" here[/TD]
[/TR]
</tbody>[/TABLE]

Is there a good way of doing this beside using VBA? or do anyone have an example VBA I could use to accomplish this?

 

[Fluff]

Hi & welcome to MrExcel
How about

Excel 2013/2016
	A	B	C
1
2	Sum of en_US
3	Task1	123	en_US
4	Task2	321	en_US
5	Sum of en_GB
6	Task1	345	en_GB
81

Cell Formulas
Range	Formula
C2	=IF(LEFT(A1,3)="sum",MID(A1,8,100),IF(B2<>"",C1,""))

 

[jimsjoh]

That works fantastic, thank you for the quick response!

 

[jimsjoh]

Would you also help clarify the logic behind the function as well?
I'm trying to learn, I think I understand it but confirmation would be useful.

IF(LEFT(A1,3)="sum" - return true if the first 3 character in cell A1 from left = sum
MID(A1,8,100) - If above is true then copy the characters starting form the 8 character in cell A1
IF(B2<>"",C1,"")) - return true if B2 is smaller or bigger value than C1? which I guess would be true as long as the cell in B has a value?

Also thank you for the welcome

 

[Fluff]

The first two are correct, the third is saying if B2 is nothing (or empty) return C1 otherwise return nothing

 

[jimsjoh]

I see, was wondering how it was continuing to copy when A was not "sum", but it's clear now, thank you!

 

[Fluff]

You're welcome & thanks for the feedback