Print Page - Combine rows in excel to one row

Title: Combine rows in excel to one row
Post by: David on February 13, 2019, 03:37:03 PM

Ok, I got one pain in the butt excel file.
It has several rows (not columns) that need to be combined into one cell, which if it was only a few, it wouldn't be that bad using the ole:
=A1&" "&A2&" "&A3&" "&A4&" "&A5 (except in this case it would be like e2, e3, e4, e5 and then e10, e11, e12, e13)

but, this file has lots of rows, some are 4 rows in one group, some are 3 rows in another group, then 4 rows, then another 4, etc...
( like e2, e3, e4 & e5 combined and then e10, e11, e12 & e13 combined, then e18 & e19, then another group...)

lather rinse repeat.
and this is for a variable data merge.

any suggestions?
a script perhaps?
a bigger margarita maybe?

:drunk3:
:hangme:

TIA

Title: Re: Combine rows in excel to one row
Post by: Joe on February 13, 2019, 04:01:32 PM

Definitely a bigger margarita. It won't help but you won't care anymore.

Sorry that is all I got.

Title: Re: Combine rows in excel to one row
Post by: David on February 13, 2019, 04:17:10 PM

all I need is another reason to drink!

thanks Joe!

Title: Re: Combine rows in excel to one row
Post by: scottrsimons on February 14, 2019, 07:16:15 AM

Something like this, I would say the Excel out as a CSV, and then open it is BBEdit and do a GREP search based on a pattern like if there is a blank row in between the rows you need to combine:

Find (this would be for four rows only):

Code Select

^(.*)&/r^(.*)&/r^(.*)&/r^(.*)&/r/r /1 /2 /3 /4
^ = beginning of the line
(.*) = all characters, and the parens allows you to replace it as one pattern
& = end of the line
/r = hard return
/r/r = at the end and in between the rows you need to combine, hopefully there is a blank row

Replace:

Code Select

/1 /2 /3 /4/r/1 = replace with pattern 1
/2 = replace with pattern 2
/3 = replace with pattern 3
/4 = replace with pattern 4
This will also put a space between the patterns and then a hard return at the end.

Title: Re: Combine rows in excel to one row
Post by: pabney on February 14, 2019, 10:09:42 AM

If you have access to google sheets or Office Cloud, you can use the textjoin function.

https://support.google.com/docs/answer/7013992?hl=en

Title: Re: Combine rows in excel to one row
Post by: David on February 14, 2019, 04:08:05 PM

thanks for all the help!

I am attempting this and I'll let you know what works.

Cheers!

Title: Re: Combine rows in excel to one row
Post by: pspdfppdfxhd on February 15, 2019, 08:02:20 AM

I hope it is simpler, all that code is giving me a headache!

Title: Re: Combine rows in excel to one row
Post by: swampymarsh on February 15, 2019, 08:21:01 AM

Try:

Google Sheets > Add Ons > AbleBits: Merge Values

Code Select

https://www.youtube.com/watch?v=VTZnCyfeE48

B4Print.com

General Category => General Prepress => Topic started by: David on February 13, 2019, 03:37:03 PM