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]

Is the table you posted only your expectation?

What is in Column I in your original data? You only seem to have 5 columns, so only columns C to G, and no I?

Please use xl2bb if at all possible. If not please label your columns. Please help the forum help you, and give a complete view of your question.

Thanks in advance.

 

[Bering]

Is the table you posted only your expectation?

What is in Column I in your original data? You only seem to have 5 columns, so only columns C to G, and no I?

Please use xl2bb if at all possible. If not please label your columns. Please help the forum help you, and give a complete view of your question.

Thanks in advance.

Thank you and apologies for the typo, yes what I posted is my expectation. Columns C to G, thanks

 

[awoohaw]

I still cannot help. What is your original data? What is in column I?


It also seems like you have triple merged rows or double line breaks after "Entity Name" and a double merged rows or a single line break after "Counterparty".
Why?

 

[Bering]

I still cannot help. What is your original data? What is in column I?

I am trying to create unique values in column A by concatenating the corresponding Column Headings with the amount in Column G, I typed I by mistake, sorry

 

[awoohaw]

As I have said, please post your starting data?

 

[Bering]

I am trying to create unique values in column A by concatenating the corresponding Column Headings with the amount in Column G, I typed I by mistake, sorry

Also the concatenation is not a problem once the main issue of repeating the relevant headings is sorted

As I have said, please post your starting data?

sorry but the original data is confidential.

As I have said, please post your starting data?

My bad, original data, as I said the number of rows is variable, HTH

Entity 1
Counterparty	Date	Transaction type	Amount	Currency
	30/09/2023	AA	100	USD
	30/09/2023	BB	50	USD
	30/09/2023	AA	25	EUR
	30/09/2023	CD	46	USD
	30/09/2023	AA	5000	JPY
Entity 2 Counterparty	Date	Transaction	Amount	Currency
	30/09/2023	BB	900	USD
	30/09/2023	BB	250	CHF

 

[Bering]

Also the concatenation is not a problem once the main issue of repeating the relevant headings is sorted

sorry but the original data is confidential.

My bad, original data, as I said the number of rows is variable, HTH

Entity 1
Counterparty	Date	Transaction type	Amount	Currency
	30/09/2023	AA	100	USD
	30/09/2023	BB	50	USD
	30/09/2023	AA	25	EUR
	30/09/2023	CD	46	USD
	30/09/2023	AA	5000	JPY
Entity 2 Counterparty	Date	Transaction	Amount	Currency
	30/09/2023	BB	900	USD
	30/09/2023	BB	250	CHF

Also the concat is with column F not G... sorry but that is not really an issue, I can deal with the contact. I just need the relevant Entity Name to be repeated in column A. Sorry fot the confusion.

 

[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