Calling all excel experts

I’ve got a big load of data and I need some help from a guru to convert it into the format I need it in, one of you attractive, smart and well rounded people will surely be able to help?

I’ve been given a big file where customers with multiple accounts are shown on different rows and I need them to be in a single row, with the data in columns instead.

The data looks like this (The slashes show the current column breaks):

Account number / Number of accounts / Data item 1 / Data item 2
123 / 2 / 100 / Y
123 / 1 / 150 / N
500 / 1 / 100 / Y
525 / 3 / 150 / Y
525 / 2 / 150 / N
525 / 1 / 150 / N

I need it to look like this

Account number / Number of accounts 1 / Data item 1 / Data item 2 / Number of accounts 2 / Data item 1 / Data item 2 / Number of accounts 3 / Data item 1 etc

For example:

123 / 2 / 100 / Y / 1 / 150N
500 / 1 / 100 / Y
525 / 3 / 150 / Y / 2 / 150 / N / 1 / 150 / N

Know what I mean?

Can anyone help?

quit

excperts

Get an intern to do it manually

how many rows you talking here?

Depends how nicely they ask the intern, I suppose.

4 Likes

28000!

I’ll do it. 1p per row.

ok, it would have to be a VBA. that creates a unique list of the account numbers, then goes through the data to do it for you.

Not really. Can you screenshot some anonymised data

image

5 Likes

Could probs set something up in power query to sort it out

https://www.extendoffice.com/documents/excel/3153-excel-concatenate-if-same-value.html

oh yeah, true. That’s DAX, isn’t it? I’ve not really used it, but have done something in DAX in Power BI that is pretty similar to this.

This looks promising I’ll have a go at this one, thanks

Worth reminding them this is where they’re better off using SQL rather than toughing it out with Excel.

Yeah I think the back end is dax but you can set 90% of stuff up without needing to write any dax yourself, cause the UI is pretty straightforward. Or Alteryx if you have access to it @still_he_persisted

" With Kutools for Excel 's Advanced Combine Rows"

Sounds like you have to install an external thing, unfortunately.

until you read underneath that and it tells you how to do it

3 Likes

Do you need to do this on an ongoing basis or just as a one off?

And is the original data sorted by account or can you have the same account in row 2 and again in row 25000?