Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Formula to drop text to a common Row.

Posted on 2016-08-01
Medium Priority
41 Views
EE Pros,

I have two results in two columns that are the results of formulas.  I want the results to appear on the same row at the bottom. What formula can I use that looks for the text in the column and then produces it on the same row line?

B.
Formula-to-drop-Text.jpg
0
Question by:Bright01
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3
• 3

LVL 47

Expert Comment

ID: 41738113
Can there ever be more than one row of text in the columns? What formulas do you have in columns E & F? It would be easier if you post a sample workbook for us to work from.
0

Author Comment

ID: 41738125
No.... the current formula only returns one row of text in the designated column.  I simply want the results in the column, where there appears a Text result, drop to a common row from two columns.

B.
0

LVL 47

Expert Comment

ID: 41738140
OK, understood. It just might have been simpler if we had those formulas as we could potentially bypass that to populate row 64.

Otherwise, try these in column E and F...

=INDEX(E54:E63, SUMPRODUCT((E54:E63<>"")*ROW(E54:E63))-ROW(E53))
=INDEX(F54:F63, SUMPRODUCT((F54:F63<>"")*ROW(F54:F63))-ROW(F53))

Wayne
0

Author Comment

ID: 41738911
Here is what I get when I apply the formulas to the bottom row.  I shifted the rows in the formula to reflect the right columns.  I see I didn't give you the right columns when I sent you the first screen shot.

B.
Indexing-a-row-to-drop-Text.jpg
0

LVL 47

Accepted Solution

Wayne Taylor (webtubbs) earned 2000 total points
ID: 41739931
In future, please post your workbook. It doesn't have to be the workbook - it can just be an example with any sensitive information scrubbed. This way we know exactly what ranges and formulas we are working with and we'll have your solution quicker.

I've recreated your workbook based on your picture to show how the formulas return the desired results. I've started from row 1 in this example as your new picture doesn't show row headers.

See attached.
example.xlsx
0

Author Closing Comment

ID: 41746271
Wayne,

Thank you!  Works great.  Appreciate the effort.

B.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calculâ€¦
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
###### Suggested Courses
Course of the Month4 days, 19 hours left to enroll