Excell formula help

Copper Contributor

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

24 Replies

@ttlj2

 

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)

Align multiple non-adjacent cells into one row.png

 

Hope that helps

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. 

@ttlj2

 

I mean copying the formula to the next right cells by dragging the first cell using the Fill Handle.

Thank you. Is there to do it for all 12,000 lines or do I need to re-write the formula?

@ttlj2

 

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!

Thank you anyway. Long story short is I have an account list of clients who have purchased from us. Name, Address, Suburb, Post Code, Phone Number etc etc and want to get them as mentioned above on one line across the sheet so I can then sort them by suburb. Eg - A1 = Name, B1 Address, C1 Suburb, D1 Post Code, E1 Phone Number. Too many clients to go through and cut & paste unfortunately.

@ttlj2

 

Can you provide a sample of the clients' data in an attached workbook, to see if that's possible?

Thanks @Haytham Amairah - Each black line is a new client. I have over 15,000 lines in total which is roughly 1800 + clients. Really appreciateScreen Shot 2019-11-19 at 7.32.24 am.png your help.

 

@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.

Thank you so much. Excuse my novice question but how do I "paste" your formula in to grab my data?

Screen Shot 2019-11-19 at 7.32.24 am.png 

This is an example of what I am working with and have over 15000 similar lines.

An unsolicited editorial comment: your original sheet is almost a textbook example of how NOT to use Excel. Whoever created it (it would appear) was thinking "3x5 card" layout, and using Excel because it had rows and columns, I guess. It would have made just as much sense to use Word. Or 3x5 cards.

I say this not for your sake, ttlj2 -- you obviously are realizing you need to get records into a single row so you can sort, sift, summarize by zip code (all sorts of purposes)--but I'm writing this more to the beginners who might drop by and look at this string of notes. Beginners with Excel often come to the software with the previous paper method in mind, just using the software because it's cool, or easy to print out the records, easy to update them....but in the process make it next to impossible to really take advantage of all that Excel offers.

You're to be commended for addressing it. I hope my solution (above) makes sense to you.

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.

@ttlj2Hmmm...that IS a novice question. I guess the answer for the novice is

  1. Highlight the items you want to copy
  2. Use the Edit....Copy menu
  3. select Edit...Copy from the menu
  4. go to where you want to paste
  5. select Edit...Paste

 

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.

@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.

@mathetes Thank you

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,

  1. have both workbooks open
  2. left click on "Converter"
  3. select "Move or Copy"
  4. pick the name of the OTHER workbook (your original)
  5. down at the bottom of the dialog box click on "Create a copy"  [this saves the original, in case something goes wrong]

If all goes as I think it will, that will be the solution. If it doesn't work, let me know.

 

 

 

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. :)

@ttlj2 

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.