Nov 17 2019 05:58 PM
Hi, I would like help to merge multiple rows and columns into one continuous row. Eg - all data to be spread across A1,B1,C1,D1,E1,F1,G1,H1,I1,J1,K1 etc FROM: B4,C4,A6,B6,A7,B7,B8,A9,B9,A11,B11 - Is there an easy formula to do this? Thank you
Nov 17 2019 06:32 PM
Hi,
Start this formula in cell A1 then drag it to the right:
=CHOOSE(COLUMN(),$B$4,$C$4,$A$6,$B$6,$A$7,$B$7,$B$8,$A$9,$A$11,$B$11)
Hope that helps
Nov 17 2019 06:40 PM
Thank you @Haytham Amairah - What do you mean "drag it to the right"? I have 12,000 lines of data I am trying to format across the rows.
Nov 17 2019 06:49 PM
I mean copying the formula to the next right cells by dragging the first cell using the Fill Handle.
Nov 17 2019 06:53 PM
Nov 17 2019 07:34 PM
If you have cells scattered in 12,000 rows, and you want to make them arranged in one line!
This formula will not help!
I'm not sure if there is a solution to this case!
Nov 17 2019 09:20 PM
Nov 18 2019 09:09 AM
Can you provide a sample of the clients' data in an attached workbook, to see if that's possible?
Nov 18 2019 01:36 PM
Thanks @Haytham Amairah - Each black line is a new client. I have over 15,000 lines in total which is roughly 1800 + clients. Really appreciate your help.
Nov 18 2019 02:11 PM
@ttlj2 IF your spreadsheet continues with exactly those same gaps (12 rows between identical items) then you can copy the formulas in the second sheet in this workbook attached.
I've used the INDIRECT function to increase the row references by 12 in each new row. To the right and across the top I've created a "helper column" and a "helper row" to assist in creating the address you are directing the formula to in the INDIRECT function.
It's crucial, though, that you have these new records 12 rows apart.
Nov 18 2019 02:19 PM
Nov 18 2019 02:21 PM
This is an example of what I am working with and have over 15000 similar lines.
Nov 18 2019 02:21 PM
Nov 18 2019 02:26 PM
unfortunately @mathetes my data is extracted from our business program that can "convert" it to Excell. Clearly this has not helped me to be able to sort into some workable format... Very frustrating.
Nov 18 2019 02:28 PM
@ttlj2Hmmm...that IS a novice question. I guess the answer for the novice is
There are keyboard shortcuts. If you use Word, they're the same. Ctrl C to copy, Ctrl V to paste. In Mac environment, Command-C, Command-V.
It might be easier for you to post an edited copy of your spreadsheet (not just an image, an actual spreadsheet)...include about five to ten of the records, falsifying the names and any other identifiable info. Then I can put the formulas into place and get it started.
Nov 18 2019 02:31 PM - edited Nov 18 2019 02:33 PM
@ttlj2 That might actually be a blessing. If it's extracted (exported) from another program, there may be a way--I'd be surprised if there isn't--to export specifically in an excel table format. See if there's an option for exporting to XLS or CSV files.......
What you've got is more of an export to print, and it does a good job of that; it just so happens that Excel can open it, but it (as you're finding) ain't useful.
So look for an export option to XLS, XLSX, or CSV.
And if that's not available, if you can post a section of it (cleaned so as not to reveal confidential or personal info) I can get the formulas started for you.
Nov 18 2019 03:12 PM
OK,@ttlj2 I've done the first ten records and copied the formula that can (or should be able to) handle up to nearly 2,000.
What I suggest you do is this. First, make a copy of your original file.
Second, in one of your copies, rename the tab that contains the records in your original workbook...rename it to "RawData"
Third, copy my "Converter" tab to that workbook.
To do this last,
If all goes as I think it will, that will be the solution. If it doesn't work, let me know.
Nov 18 2019 03:20 PM
By the way, @ttlj2, I do still think that you should be able to get that other program your company uses to export the data in a true Excel format, rows with a header just as we're trying to create here. Most programs DO have that capability.
I know the formulas I created will do the job, but even though I had fun creating those formulas, that IS doing it the hard way. :)
Nov 18 2019 03:55 PM
A similar solution but I happened to choose to use INDEX to return data from column B.
= INDEX(data, 12*(SNum-1) + {1,4,5,6,8} )
The result is in the form of a single array formula.
Using Office 365 it was a single dynamic array that spilt from the formula in B2.
SNum is a serial number, I calculated using
= SEQUENCE( ROWS(data)/12 )
but then reduced it to values.