08-27-2018, 01:00 PM | #1 | ||||||||||||||||||||
Morgado's Favorite Forum Fascist
Join Date: Oct 2000
Location: Greensboro, NC
|
Excel Question
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
__________________
The media don't understand the kinds of problems and pressures 54 million come wit'! |
||||||||||||||||||||
08-27-2018, 02:14 PM | #2 |
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 |
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 |
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) | |
Thread Tools | |
|
|