• 467K Members
• 11.3K Online
• 565K Conversations

## Excell formula help

Occasional Contributor

# Excell formula help

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
Highlighted

# Re: Excell formula help

@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)`

Hope that helps

# Re: Excell formula help

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.

# Re: Excell formula help

@ttlj2

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

# Re: Excell formula help

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

# Re: Excell formula help

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

# Re: Excell formula help

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.

# Re: Excell formula help

@ttlj2

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

# Re: Excell formula help

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.

# Re: Excell formula 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.

# Re: Excell formula help

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

# Re: Excell formula help

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

# Re: Excell formula help

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.

# Re: Excell formula help

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.

# Re: Excell formula help

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

1. Highlight the items you want to copy
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.

# Re: Excell formula help

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

# Re: Excell formula help

@mathetes Thank you

# Re: Excell formula help

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.

# Re: Excell formula help

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.

# Re: Excell formula help

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.

# Re: Excell formula help

@Peter Bartholomew   That's a great solution too, Peter. I love how Excel has multiple ways to solve a problem. Your use of INDEX is a bit more elegant than mine with INDIRECT, in that you needed no "helper" columns or rows. I could have written that as a single formula (in retrospect), but was also trying to keep it understandable (as much as possible) for the original poster.....

Anyway, thanks for sharing that. I am also loving the learning that goes on in sharing solutions to problems people bring to these boards.

# Re: Excell formula help

Thank you for your comments.  I have a nasty feeling that much of the material I post is unintelligible for the end user who will approach problems bottom-up in the time-honoured spreadsheet manner.  Something that I find interesting is that the new dynamic array functionality requires top-down thinking.  Instead of array methods being methods of last resort, they can come to the fore and offer a far more structured approach to problem solving.

You drew attention to the fact that Excel offers multiple ways to solve many problems.  Since the current problem is essentially one of ETL, I have also included a Power Query solution in the attached workbook.  I feel it should be possible to pivot the first table without aggregation but my limited knowledge of PQ and the M language probably resulted in more steps than were strictly needed.

# Re: Excell formula help

And here is one more Power Query variant. Assuming source file is in C:\Test folder (perhaps it's possible to connect directly to database from which source file is generated).

Another assumption is empty rows are always empty, i.e. we don't have two or more phone numbers or email addresses, etc. for the same unit.

After that give names to fields which are not specified, add index for each group and pivot.

# Re: Excell formula help

Thanks for that.  I got close but then pivoted on the wrong column (I think I selected the ID rather than the attribute column I wished to split into fields).  The outturn was an error so I reverted to plan B and used a sequence of outer joins to build the output table.  Data analysis is definitely not my strong suit; I can't remember ever having a significant data set to slice and dice so the skills come slowly.

# Re: Excell formula help

Peter, I published it since started doing the task before seen your variant. At the same I think more variants in discussions the better, it's always useful to check an issue from different points of view.

Your strong point is analysis. That's only bit more practice to add the word "data" in front.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies