Repeat Excel Spreadsheet Column Headings on each row until they change

[Bering]

Hello,
I have an excel extract from an accounting system that contains sets of figures for multiple entities, in sequence one below the other. The entity's name appears once in column C, below there is the string "Counterparty" and the rest of the column is blank until the next set of data.
Figures are in column C: M, the number of rows for each set is variable.

I am trying to create unique values in column A by concatenating the corresponding Column Headings with the amount in Column I.
Below I am showing the result I am trying to achieve, possibly with formulas, but vba would also be fine as.
Appreciate any suggestions, thanks!

Entity 1
Counterparty	Date	Transaction type	Amount	Currency
Entity1100	30/09/2023	AA	100	USD
Entity150	30/09/2023	BB	50	USD
Entity125	30/09/2023	AA	25	EUR
Entity146	30/09/2023	CD	46	USD
Entity15000	30/09/2023	AA	5000	JPY
Entity 2 Counterparty	Date	Transaction	Amount	Currency
Entity2900	30/09/2023	BB	900	USD
Entity2250	30/09/2023	BB	250	CHF

 

[awoohaw]

So, this is taking the column heading of G (Which I am guessins is always "Currency", with the value of column concatenated and putting it in column A.

I am sure that is not what you want. But I have to guess because I don't understand. Please post your data (sanitize it to keep your confidentiality). And PLEASE PLEASE PLEASE label your columns with the column letters!

Book1
	A	B	C	D	E	F	G	H
1			Entity 1
2			Counterparty	Date	Transaction type	Amount	Currency
3	Currency USD			30/09/2023	AA	100	USD
4	Currency USD			30/09/2023	BB	50	USD
5	Currency EUR			30/09/2023	AA	25	EUR
6	Currency USD			30/09/2023	CD	46	USD
7	Currency JPY			30/09/2023	AA	5000	JPY
8			Entity 2	Date	Transaction	Amount	Currency
9			Counterparty
10	Currency USD			30/09/2023	BB	900	USD
11	Currency CHF			30/09/2023	BB	250	CHF
Sheet2

Cell Formulas
Range	Formula
A3:A11	A3	=IF(C3="","Currency " &G3,"")

 

[Bering]

Book2
	A	B	C	D	E	F	G
1			Entity 1
2			Counterparty	Date	Transaction type	Amount	Currency
3				30/09/2023	AA	100	USD
4				30/09/2023	BB	50	USD
5				30/09/2023	AA	25	EUR
6				30/09/2023	CD	46	USD
7				30/09/2023	AA	5000	JPY
8			Entity 2
9			Counterparty	Date	Transaction type	Amount	Currency
10				30/09/2023	BB	900	USD
11				30/09/2023	BB	250	CHF
Sheet1

 

[Bering]

Thanks, I think I have it figured out. But do you want the currency type(Column G) attached to the Entity? or the Amount of that currency (Column F)?

Try this:

Book1
	A	B	C	D	E	F	G
1			Entity 1
2			Counterparty	Date	Transaction type	Amount	Currency
3	Entity 1100			30/09/2023	AA	100	USD
4	Entity 150			30/09/2023	BB	50	USD
5	Entity 125			30/09/2023	AA	25	EUR
6	Entity 146			30/09/2023	CD	46	USD
7	Entity 15000			30/09/2023	AA	5000	JPY
8			Entity 2	Date	Transaction	Amount	Currency
9			Counterparty
10	Entity 2900			30/09/2023	BB	900	USD
11	Entity 2250			30/09/2023	BB	250	CHF
Sheet2

Cell Formulas
Range	Formula
A3:A11	A3	=IF(C4="Counterparty","",IF(C2="Counterparty",C1&F3,IF(A2<>"",SUBSTITUTE(A2,F2,F3),"")))

that's perfect, thank you so much for your help, time and patience!!!!