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]

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),"")))

 

[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!!!!

 

[awoohaw]

Happy to help. Best wishes!