Copy Non-blank Cells From a Range

[Weebs21]

Hi, I am extremely new to this forum so I haven't had much chance to look around for an answer let alone I'm not sure how to word the question....I have a spreadsheet comprised of quite a bit of data, and I need a formula that will pull data from a set range of cells in a row if one of them has data. For example, let's say my range is from A1:A20. Every cell is empty but cell A8. It's the only cell in the range that has anything in it so I want a formula in cell A21 that will return the data from that one cell. Not to leave anything out, but there are formulas in every cell from A1:A20. They all are set to return empty strings based off IF functions.

[Glenn Kennedy]

Your title is meaningless. Since this is a help forum.... most people want help. A title like "Copy non-blank cells from a range" would be acceptable.
You can expect to be asked to edit your thread title before we can help you. So, pre-empt things and select thread tools from the top of your first post in this thread and amend the title.

[Weebs21]

Does this appease you?....

[Glenn Kennedy]

It's not me... it's the Forum moderators.

=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$1:$A$20)/($A$1:$A$20<>""),ROWS($1:1))),"")

see sheet.

[Weebs21]

That's crazy. I mean I was struggling with how to even word the question, let alone enter a proper title....

Now, here's the stupid question I must ask....what does the A:A mean after INDEX? I want to incorporate this into what columns I'm using to see if it works.

[Glenn Kennedy]

There's no such thing as a stupid question. There are plenty of stupid answers, though.

A:A is the column from which you want to get the result.

[Glenn Kennedy]

=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$1:$A$20)/($A$1:$A$20<>""),ROWS($1:1))),"")

Red: Look at cells A1:A20 and if they're non-blank
Yellow: return the row number, otherwise return an error
Cyan: then in order of increasing (15) row number, ignoring errors where blank cells were found (6)
Orange: Return the CORRESPONDING values from column A
Blue: starting with the lowestrow number
Black: if an error results, return a blank.

[Weebs21]

Well, I appreciate that comment. I'm very new to advanced formulas in Excel, and I don't understand every part of the formula always. I'm fairly proficient with IF functions as well as VLOOKUP functions as I use them quite frequently. I guess the issue I'm having is I don't know which column I want to return data from. Like the example I gave in my original post, there may be data in only cell A8, but that might not be the case for the next row down or the row after that. It varies for every row. It's almost as if I need a lookup type function that returns data from a range of cells in a row where only one of the cells has data and the other ones are empty string cells ("").

[Glenn Kennedy]

Now you're getting me confused.

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? However, please give us an indication of the approximate number of rows of data you want the solution to work with (100, 1000, 100,000 or whatever). Please don't attach a picture of an Excel sheet (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[Weebs21]

Ok, I've attached what I'm working with. Basically I need P2 to return data from J2:O2 only if there is data in them. The way the workbook is setup, there will only be data in either J, K, L, M, N, or O. There will never be data in more than one of those columns. I need column P to do that somehow. My currently formula in column P I think would work only if the other cells in the formula were actually blank, not empty string blank (""). I unfortunately cannot leave them blank, though. The IF functions need to be in columns J:O.

[Glenn Kennedy]

A totally different question !!!

=J2&K2&L2&M2&N2&O2

copied down.

[Weebs21]

Oh boy....I'm very sorry for all the misleading questions then. That's where I was confused at from the start.

I just put it in, and it worked beautifully. I don't know why it was something so simple that I overlooked. I was definitely overthinking it. I would like to apologize for leading you in the wrong direction the entire time, Glenn. I'm also sorry for coming off as rude earlier when you were helping me with the title correction. I will remember for that for future posts, and also to fully know how to ask a question correctly!!

Thank you very much for your assistance, Glenn.

[Glenn Kennedy]

You're welcome. I was going to make a comment about a bit of rudeness, but as I've had a couple of beers, on a ferry - in a very rough sea crossing - I'm feeling well-disposed towards humanity. So I didn't bother.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[Weebs21]

Ha! Well, I appreciate you not bothering. I definitely was going to say something as it wasn't uncalled for.

It definitely takes care of my problem so I will mark it as solved, and I will gladly click Add Reputation on your post, sir. Take care!

[Glenn Kennedy]

Cheers!! Time to eat...