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

 

[awoohaw]

Happy to help. Best wishes!

 

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