XML table / conditional TEXTJOIN function

HJimHB

New Member
Joined
Dec 2, 2019
Messages
9
Office Version
365
Platform
Windows
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
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,275
maybe use Power Query (Get&Transform) with Group option
 

HJimHB

New Member
Joined
Dec 2, 2019
Messages
9
Office Version
365
Platform
Windows
To clarify how the table looks as it didn't come through clearly:

REFSURNAMEFORENAME
1
Mouse
Mickey
2
Duck
Donald
 

HJimHB

New Member
Joined
Dec 2, 2019
Messages
9
Office Version
365
Platform
Windows
thanks Sandy, I'll have a look at that
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,933
Messages
5,508,196
Members
408,670
Latest member
lhmwnrexcel

This Week's Hot Topics

Top