Excel Question

Ben E Lou · 08-27-2018, 01:00 PM

I've got data that should be columns, but is in consecutive rows. In other words, ideally I'd like to have CLI-AURORA in column 1, and 4315 in column 2, then on the next row, CLI-BAKERF in column 1 and 5487 in column 2, and so on. However, in some cases, such as CLI-BATONR, I'd like five rows. So, from the raw data sample at the bottom, the last six rows should be...

CLI-BATONR, 174
CLI-BATONR, 2471
CLI-BATONR, 7358015556286700
CLI-BATONR, 7358095000734300
CLI-BATONR, 7358095021356000
CLI-BAYOUC, 2436

How can I pull this off?

RAW DATA SAMPLE

CLI-AURORA
4315
CLI-BAKERF
5487
CLI-BARKSD
7206
CLI-BARTHO
6371
CLI-BASTRO
7230
CLI-BATONR
174
2471
7358015556286700
7358095000734300
7358095021356000
CLI-BAYOUC
2436

henry296 · 08-27-2018, 02:14 PM

Ben,

Would text to columns work or am I misunderstanding?

MIJB#19 · 08-27-2018, 02:22 PM

Are the first columns all in a similar format?

I would probably make a couple of formula's, first one to collect the first column if it fits the format of column 1, then if that column gets a value, add the other columns. Assuming the first data value is in A1:
in B1 something like =if(A1='cli*',A1,"")
in C1 something like =if(B1="cli*","",A2)
in D1 something like =if(B1="cli*","",A3)
in E1 something like =if(B1="cli*","",A4)
then copy paste these formulas to all the rows and eventually copy-paste the values into a new sheet, getting rid of the rows with just "".

Applicability depends on the size of the data file though.

Ben E Lou · 08-27-2018, 02:37 PM

I actually solved it by adding another column (=VAL(A2)), and then a brief php script to handle if a2=b2. (No, Henry, text to columns wouldn't work, since all the data is in one column. Sometimes it was text, sometimes it was numerical.)

08-27-2018, 02:14 PM	#2
henry296 College Starter Join Date: Oct 2000 Location: Pittsburgh, PA	Ben, Would text to columns work or am I misunderstanding? __________________ "It's a great day for hockey" - "Badger" Bob Johnson

08-27-2018, 02:22 PM	#3
MIJB#19 Coordinator Join Date: Oct 2000 Location: Maassluis, Zuid-Holland, Netherlands	Are the first columns all in a similar format? I would probably make a couple of formula's, first one to collect the first column if it fits the format of column 1, then if that column gets a value, add the other columns. Assuming the first data value is in A1: in B1 something like =if(A1='cli',A1,"") in C1 something like =if(B1="cli","",A2) in D1 something like =if(B1="cli","",A3) in E1 something like =if(B1="cli","",A4) then copy paste these formulas to all the rows and eventually copy-paste the values into a new sheet, getting rid of the rows with just "". Applicability depends on the size of the data file though. __________________ * 2005 Golden Scribe winner for best FOF Dynasty about IHOF's Maassluis Merchantmen * Former GM of GEFL's Houston Oilers and WOOF's Curacao Cocktail Last edited by MIJB#19 : 08-27-2018 at 02:23 PM.

08-27-2018, 02:37 PM	#4
Ben E Lou Morgado's Favorite Forum Fascist Join Date: Oct 2000 Location: Greensboro, NC	I actually solved it by adding another column (=VAL(A2)), and then a brief php script to handle if a2=b2. (No, Henry, text to columns wouldn't work, since all the data is in one column. Sometimes it was text, sometimes it was numerical.) __________________ The media don't understand the kinds of problems and pressures 54 million come wit'! Last edited by Ben E Lou : 08-27-2018 at 02:37 PM.

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)