Solved: Getting 2 pieces of data from one source (cell)

[marshybid]

Hi All,

I need to be able to get 2 pieces of data from 1 cell and filter it by speific criteria (example workbook attached)

I need to be able to extract an Entity reference and a cost centre. I have manulally completed a few in the workbook to show what the end data should look like.

The issue is that the data in the cell I am extracting from (highlighted yellow in workbook) does not always have the data in the same order.

Example 1:

Cost Centre cell (in yellow in workbook) contains: ZH626 UK-CORP LEGAL DEPARTMENT(908000510)
In this instance I need to extract the cost centre (which is ZH626) and identify the entity (which 908000) - I have listed the entities on a worksheet in the workbook

Example 2:

Cost Centre cell (in yellow in workbook) contains: 908140146-M2292
In this instance the cost centre is (M2292) and the entity is 908140

Please let me know if this is not very clear, see attached workbook.

All help gratefully received.

Thanks,
Marshybis

[Bob Phillips]

Is the entity always at the end in brackets, or at the beginning.

Is the CC always at the start or after a hyphen (the only hyphen?)?

[marshybid]

Hi xld,

You've hit the problem right on the head, the data is extracted from a dbase directly into excel in .csv format. The information contained in the cell (in yellow) does not always have a consistent format.

I have been tyring to think of a way of coding it so that I can say if the cell contains the entity data (on worksheet - Entities) then entity = ###

I am pulling my hair out on this, and I don't have much to pull out

Marshybid

[Bob Phillips]

Oops, didn't even notice the Entities sheet. With that help, entity is simple

In AS2, enter this array formula and copy down

=MAX(IF(ISNUMBER(FIND(Entities!$A$2:$A$30,Data!AM2)),Entities!$A$2:$A$30))

and help on CC?

[marshybid]

Hi xld,

When I enter the formula into AS2 and copy down the result is 908500 or 0 for all rows??

Any thoughts?

Marshybid

[Bob Phillips]

Array-enter it, Ctrl-Shift-Enter.

[marshybid]

Array-enter it, Ctrl-Shift-Enter

Sorry xld I don't understand what you mean by this

When I do this it doesn't do anything!!

Marshybid

[Bob Phillips]

When you enter a formula, you normally type it in then hit the Enter key. With array formulae, you need to hit Ctrl-Shift-Enter together. This will put braces {...} around the formula. If yo re-edit the formula, they diappera, so you have to Ctrl-Shift-Enter again to get it back.

Here is your workbook with formulae

[marshybid]

Thanks xld

If I write the formula into my macro can I code the ctrl-shift-enter??

Marshybid

[Bob Phillips]

No, not that way.

Are you trying to insert the formula, or the resultant value?

[marshybid]

Hi xld,

I think I may have found a common factor for filtering these cells using a vlookup.

For all of the Cost Centre cells there appear to be only 2 formats used;

Format 1 = UK723 UK-NE AIRWAVE PROJECT OFFICE NEW BUS(908000140)

In this format I need to write a formula that says If last character in cell is ) then -Entity - Right(cell, 10) then Left(cell, 6) - I still need to somehow remove the ) and the cost centre = Left(cell, 5)

Format 2 = 908000120-GU201

In this format last character does not = ) therefore Entity = Left(cell, 6) and Cost Centre = Right(cell, 5)

Does this make sense??

I can then use a vlookup to find entity and add entity name (or maybe you can show me how I could do that using case perhaps??)

Thanks,

Marshybid

[Bob Phillips]

VLOOKUP?

=IF(RIGHT(AM2,1)=")",MID(AM2,LEN(AM2)-9,6),LEFT(AM2,6))

=IF(RIGHT(AM2,1)=")",LEFT(AM2,5),RIGHT(AM2,5))

[marshybid]

xld, you are a star once again

That formula works brilliantly.

I have a question about filtering and pivoting this data, but I will mark this as solved and post a new thread.

Thanks loads.

Marshybid