p01 Kansas City Southern
Skip to main content

Recent Topics

xx
Printing scratch and win cards
by Tracy
February 22, 2019, 03:15:04 PM

xx
Mojave (and Acrobat DC)
by david
February 22, 2019, 09:26:02 AM

xx
Imposition Books - XMF
by Joe
February 21, 2019, 01:03:27 PM

xx
CWS 5
by wonderings
February 20, 2019, 04:02:55 PM

xx

xx
Markstools2 - State of the Industry?
by scottrsimons
February 19, 2019, 08:00:06 AM

xx
KBA CIP3 software
by Joe
February 18, 2019, 04:10:36 PM

xx
Dell PowerEdge R730 RAID
by mattbeals
February 18, 2019, 02:43:01 PM

xx
Prinergy 8.1.2 for sale
by abc
February 15, 2019, 05:50:39 PM

xx
Combine rows in excel to one row
by swampymarsh
February 15, 2019, 09:21:01 AM

Topic: Combine rows in excel to one row (Read 158 times) previous topic - next topic

Combine rows in excel to one row

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
Prepress guy - doin' jobs one at a time

Re: Combine rows in excel to one row

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

Sorry that is all I got.
Mac OS 10.12 Sierra | Prinergy 7.5 | Adobe Creative Cloud 2017 | Two Luscher XPose 160 CTP units

Prepress: One who does precision guess work based on unreliable data provided by those of questionable knowledge.

 

Re: Combine rows in excel to one row

Reply #2
all I need is another reason to drink!

thanks Joe!
Prepress guy - doin' jobs one at a time

Re: Combine rows in excel to one row

Reply #3
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.
Mac • Prinergy 8 • Preps 8 • RBA • Fusion Pro

"Your superior intellect is no match for our puny weapons!" - Homer J. Simpson

"Those who make you mad, control you."


Re: Combine rows in excel to one row

Reply #5
thanks for all the help!

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

Cheers!
Prepress guy - doin' jobs one at a time

Re: Combine rows in excel to one row

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

Re: Combine rows in excel to one row

Reply #7
Try:

Google Sheets > Add Ons > AbleBits: Merge Values

Code: [Select]
https://www.youtube.com/watch?v=VTZnCyfeE48