I need a formula that would concatenate only the blank cells between two
rows that have data. If the line below 10001 is blank (A3), concatenate all
the rows in B whose A value is blank.
I am using this formula =IF(A3="",B2&""&B3&""&B4&"",B2)
The problem is that sometimes, I might have two or more blank cells below
10001.
Could someone help!

Thanks!

What do you mean by "below 10001"? Row 10001? The values in your cells?

Also, B2&""&B3&""&B4&"" can be shortened to B2&B3&B4

--
Regards,
Fred


"Lisa" wrote in message
...
I need a formula that would concatenate only the blank cells between two
rows that have data. If the line below 10001 is blank (A3), concatenate all
the rows in B whose A value is blank.
I am using this formula =IF(A3="",B2&""&B3&""&B4&"",B2)
The problem is that sometimes, I might have two or more blank cells below
10001.
Could someone help!

Thanks!

Fred,
It is a software problem, because if a column has more than 12 characters it
breaks the column into more rows and it looks something like this when I
apply the formula below. The problem is row A4 (10002), or any row that would
have more or less than 3 blank rows in column A.

Actual Output
A B Result
10001 Need Need Your Help
Your
Help
10002 Thank Thank You Regards
You
10003 Regards



"Fred Smith" wrote:

What do you mean by "below 10001"? Row 10001? The values in your cells?

Also, B2&""&B3&""&B4&"" can be shortened to B2&B3&B4

--
Regards,
Fred


"Lisa" wrote in message
...
I need a formula that would concatenate only the blank cells between two
rows that have data. If the line below 10001 is blank (A3), concatenate all
the rows in B whose A value is blank.
I am using this formula =IF(A3="",B2&""&B3&""&B4&"",B2)
The problem is that sometimes, I might have two or more blank cells below
10001.
Could someone help!

Thanks!

I'm sorry, Lisa, you've completely lost me.

When you say "it breaks the column into more rows", what is "it"? Is your cell
formatted to wrap text?

I don't see any blank rows in Column A. Do you mean blank rows in columns other
than A?

You say "any row that would have more or less than 3 blank rows in column A". A
row can't have any more than one row. How can a row possibly have "3 blank rows
in Column A"?

--
Regards,
Fred


"Lisa" wrote in message
...
Fred,
It is a software problem, because if a column has more than 12 characters it
breaks the column into more rows and it looks something like this when I
apply the formula below. The problem is row A4 (10002), or any row that would
have more or less than 3 blank rows in column A.

Actual Output
A B Result
10001 Need Need Your Help
Your
Help
10002 Thank Thank You Regards
You
10003 Regards



"Fred Smith" wrote:

What do you mean by "below 10001"? Row 10001? The values in your cells?

Also, B2&""&B3&""&B4&"" can be shortened to B2&B3&B4

--
Regards,
Fred


"Lisa" wrote in message
...
I need a formula that would concatenate only the blank cells between two
rows that have data. If the line below 10001 is blank (A3), concatenate all
the rows in B whose A value is blank.
I am using this formula =IF(A3="",B2&""&B3&""&B4&"",B2)
The problem is that sometimes, I might have two or more blank cells below
10001.
Could someone help!

Thanks!

Actual Output
A B C Result
1 10001 Need 5 Need Your Help
2 Your
3 Help
4 10002 Thank 10 Thank You Regards
5 You
6 10003 Regards 5

This is how the report looks like. In A1 is account Number, in Column B1 is
usually description of the purchases, in Column C cost, if there are more
than 12 characters the software breaks data in B1 into more rows depending on
the number of characters, could be 2, 3 or 4 rows. So in row 2 and 3 you have
data in column B only. The formula I used concatenates all three rows B1-3.
However, if I apply the same formula for the next row, it will copy B6 as
well. So I would have to adjust the formula every time there is a change in
the number of rows.

Thanks,
Lisa
"Fred Smith" wrote:

I'm sorry, Lisa, you've completely lost me.

When you say "it breaks the column into more rows", what is "it"? Is your cell
formatted to wrap text?

I don't see any blank rows in Column A. Do you mean blank rows in columns other
than A?

You say "any row that would have more or less than 3 blank rows in column A". A
row can't have any more than one row. How can a row possibly have "3 blank rows
in Column A"?

--
Regards,
Fred


"Lisa" wrote in message
...
Fred,
It is a software problem, because if a column has more than 12 characters it
breaks the column into more rows and it looks something like this when I
apply the formula below. The problem is row A4 (10002), or any row that would
have more or less than 3 blank rows in column A.

Actual Output
A B Result
10001 Need Need Your Help
Your
Help
10002 Thank Thank You Regards
You
10003 Regards



"Fred Smith" wrote:

What do you mean by "below 10001"? Row 10001? The values in your cells?

Also, B2&""&B3&""&B4&"" can be shortened to B2&B3&B4

--
Regards,
Fred


"Lisa" wrote in message
...
I need a formula that would concatenate only the blank cells between two
rows that have data. If the line below 10001 is blank (A3), concatenate all
the rows in B whose A value is blank.
I am using this formula =IF(A3="",B2&""&B3&""&B4&"",B2)
The problem is that sometimes, I might have two or more blank cells below
10001.
Could someone help!

Thanks!

There are probably better methods, but I would just use a If statement for each
situation:

=if(and(a2="",a3=""),b1&b2&b3,if(a2="",b1&b2,b1))

--
Regards,
Fred


"Lisa" wrote in message
...
Actual Output
A B C Result
1 10001 Need 5 Need Your Help
2 Your
3 Help
4 10002 Thank 10 Thank You Regards
5 You
6 10003 Regards 5

This is how the report looks like. In A1 is account Number, in Column B1 is
usually description of the purchases, in Column C cost, if there are more
than 12 characters the software breaks data in B1 into more rows depending on
the number of characters, could be 2, 3 or 4 rows. So in row 2 and 3 you have
data in column B only. The formula I used concatenates all three rows B1-3.
However, if I apply the same formula for the next row, it will copy B6 as
well. So I would have to adjust the formula every time there is a change in
the number of rows.

Thanks,
Lisa
"Fred Smith" wrote:

I'm sorry, Lisa, you've completely lost me.

When you say "it breaks the column into more rows", what is "it"? Is your
cell
formatted to wrap text?

I don't see any blank rows in Column A. Do you mean blank rows in columns
other
than A?

You say "any row that would have more or less than 3 blank rows in column A".
A
row can't have any more than one row. How can a row possibly have "3 blank
rows
in Column A"?

--
Regards,
Fred


"Lisa" wrote in message
...
Fred,
It is a software problem, because if a column has more than 12 characters
it
breaks the column into more rows and it looks something like this when I
apply the formula below. The problem is row A4 (10002), or any row that
would
have more or less than 3 blank rows in column A.

Actual Output
A B Result
10001 Need Need Your Help
Your
Help
10002 Thank Thank You Regards
You
10003 Regards



"Fred Smith" wrote:

What do you mean by "below 10001"? Row 10001? The values in your cells?

Also, B2&""&B3&""&B4&"" can be shortened to B2&B3&B4

--
Regards,
Fred


"Lisa" wrote in message
...
I need a formula that would concatenate only the blank cells between two
rows that have data. If the line below 10001 is blank (A3), concatenate
all
the rows in B whose A value is blank.
I am using this formula =IF(A3="",B2&""&B3&""&B4&"",B2)
The problem is that sometimes, I might have two or more blank cells
below
10001.
Could someone help!

Thanks!