Thanks RP, but that won't quite work. Reason is that the company name
and the quarter are in the same column, not next to each other.

This spreadsheet could be hundreds of companies long, and manually copy
& pasting the company name in a neighboring column would take way too
long. I need the whole entire thing to be automated.

Thanks anyway, please let me know if you have any ideas.

Phil

"General" wrote in news:1129837940.963257.5400
@o13g2000cwo.googlegroups.com:

Thanks RP, but that won't quite work. Reason is that the company name
and the quarter are in the same column, not next to each other.

This spreadsheet could be hundreds of companies long, and manually copy
& pasting the company name in a neighboring column would take way too
long. I need the whole entire thing to be automated.

Thanks anyway, please let me know if you have any ideas.

Phil





Phil,

How is this data produced? I did not mean to imply any manual
manipulation of the data on your part.

Ideally the creator of the data could do this for you. If not, we could
use some simple formulas or a macro¹ to improve the structure.

Does anyone else depend on this data? in this format?

Is there anything useful in the empty rows?


-RP

¹policy schmolicy, have whoever doesn't like VBA fix the data then

Hi RP,

The data is spit out by an outside program, and the format can't be
changed.

Use of VB is not an option.

Thx,

Phil

Hi RP,

The data is spit out by an outside program, and the format can't be
changed.

Use of VB is not an option.

Thx,

Phil

"General" wrote in news:1129841369.520961.179420
@g49g2000cwa.googlegroups.com:

Hi RP,

The data is spit out by an outside program, and the format can't be
changed.

Use of VB is not an option.

Thx,

Phil




Phil,

Insert a row so that the data starts in A2.

Put the following formula in the second row of an empty column (change B1
to reflect the actual column used) and then copy it down.

=IF(OR(LEFT(A2,1)="Q",A2=""),B1,A2)

Now you can use the two column match. Actually, you could append the
contents of A2 after the IF function. Then you could just use a regular
MATCH and avoid the array formula.

This formula assumes any non-blank cell that does not begin with a 'Q' is
a company name. The first argument would need to be refined if this is
not the case (i.e. you have companies that start with 'Q'). This should
get you going though.


HTH,
RP

You can easily create another column with the company name using the
formula below, which assumes the following:

Your list is in column A starting at A1.
No company name ends in a number (0-9)
All quarter identifications end in a number.

Put the following formula in B2 down:

=IF(ISERROR(VALUE(RIGHT(A2))),A2,B1)

This should put the company name in column B.
Now use RP's formula to find the match for your Company and quarter.

In this case, assuming your search company is in C1 and Quarter in D1,
you would write

=MATCH(C1&D1,B1:Bnn&A1:Ann,0), where nn is the ending row of your list
- entered with Ctl+Shift+Enter, since it is an array formula.

If some company name ends in a number, then you will need a different
formula to put the company name in another column, but I will wait for
your response before suggesting what that might be.

Good luck.


General wrote:
Thanks RP, but that won't quite work. Reason is that the company name
and the quarter are in the same column, not next to each other.

This spreadsheet could be hundreds of companies long, and manually copy
& pasting the company name in a neighboring column would take way too
long. I need the whole entire thing to be automated.

Thanks anyway, please let me know if you have any ideas.

Phil

An alternative simple formula, for putting the company name in column
B, which treats everything that is not in the format "Qnnn" as a
company name is

=IF(AND(LEFT(A2)="Q",ISNUMBER(VALUE(RIGHT(A2,3)))) ,B1,A2)