PDA

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



marshybid
05-16-2008, 03:13 AM
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
05-16-2008, 03:41 AM
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
05-16-2008, 03:49 AM
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
05-16-2008, 04:21 AM
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
05-16-2008, 08:05 AM
Hi xld,

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

Any thoughts? :dunno

Marshybid

Bob Phillips
05-16-2008, 08:38 AM
Array-enter it, Ctrl-Shift-Enter.

marshybid
05-16-2008, 08:50 AM
Array-enter it, Ctrl-Shift-Enter


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

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

Marshybid

Bob Phillips
05-16-2008, 10:45 AM
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
05-16-2008, 01:35 PM
Thanks xld :bow:

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

Marshybid :rotlaugh:

Bob Phillips
05-16-2008, 01:44 PM
No, not that way.

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

marshybid
05-19-2008, 02:04 AM
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
05-19-2008, 02:22 AM
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
05-19-2008, 03:24 AM
xld, you are a star once again :bow:

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