XML table / conditional TEXTJOIN function

[HJimHB]

Hi

I have a system which exports XML files that I need to convert into Excel for reconciliation etc.

The XML table function in Excel works but puts each bit of data on the next line - for example:

Ref Surname Forename
1
Mouse
Mickey
2
Duck
Donald


The TEXTJOIN function allows me to put all the information for each reference on one line but it is very time-consuming to set the ranges (the full files run to dozens of columns and thousands of rows, and you have to scroll down to the next reference to find where to stop).

Is there a way to set the function to look at the first column and only go down as far as the next change in value?

There is not a fixed number of rows between each reference so it has to look to the next reference. In addition, the references are not sequential or in the same format (although they are all numbers).

Is anyone able to suggest an easy way to do this?

Thanks in advance

 

[sandy666]

maybe use Power Query (Get&Transform) with Group option

 

[HJimHB]

To clarify how the table looks as it didn't come through clearly:

REF	SURNAME	FORENAME
1
	Mouse
		Mickey
2
	Duck
		Donald

 

[HJimHB]

thanks Sandy, I'll have a look at that

 

[sandy666]

sure, have a nice day