Formula Help-ASAP
 

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!

Formula Help-ASAP
 

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!

Formula Help-ASAP
 

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!

Formula Help-ASAP
 

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!

Formula Help-ASAP
 

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!

Formula Help-ASAP
 

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!

Formula Help-ASAP
 

Thank you Fred, this was very helpful.

"Fred Smith" wrote:

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!