Afternoon everybody....

I have a pair of text based lists running down a worksheet.. i need to
create a 3rd list that is made up from the information in lists one and
two.

Eg.

List one List two List three (PLU)

Sony ericsson P900 SE P900
Nokia 6230 NOK 6230

As you can see the LPU is extracted from the Make and model.
Is it possible to set up some formulation for this?

Thanks
James


--
themax16
------------------------------------------------------------------------
themax16's Profile: http://www.excelforum.com/member.php...o&userid=23622
View this thread: http://www.excelforum.com/showthread...hreadid=390520

What are the rules? Why is Sony ericsson SE whilst Nokia is NOK?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"themax16" wrote in
message ...

Afternoon everybody....

I have a pair of text based lists running down a worksheet.. i need to
create a 3rd list that is made up from the information in lists one and
two.

Eg.

List one List two List three (PLU)

Sony ericsson P900 SE P900
Nokia 6230 NOK 6230

As you can see the LPU is extracted from the Make and model.
Is it possible to set up some formulation for this?

Thanks
James


--
themax16
------------------------------------------------------------------------
themax16's Profile:
http://www.excelforum.com/member.php...o&userid=23622
View this thread: http://www.excelforum.com/showthread...hreadid=390520

Yes you can easily do something like this. I personally would create two
helper columns (maybe in a different sheet if you want to have things looking
clean). One that has all the unique values for list one (sony ericsson,
nokia, motorola, etc) and another that has its abbreviation (like SE, NOK,
MOT). You could select this whole array and name it ABBREV or something like
that (if you need help with this just let me know and i'll walk you through
that). Then you could use the VLOOKUP function in sheet two to create all
the matching abbreviations for every row in sheet one. FINALLY, in sheet one
list three, you could use the CONCATENATE function with a space to combine
the column from sheet two that contains the abbreviation and the model from
sheet one list two that contains the model number. Dragging that down....
You've got yourself exactly what you wanted.


If you need help with the functions i've described, you can try looking them
in the forum with search or just asking on here and I can go into more detail.


-Derek

themax16 wrote:
Afternoon everybody....

I have a pair of text based lists running down a worksheet.. i need to
create a 3rd list that is made up from the information in lists one and
two.

Eg.

List one List two List three (PLU)

Sony ericsson P900 SE P900
Nokia 6230 NOK 6230

As you can see the LPU is extracted from the Make and model.
Is it possible to set up some formulation for this?

Thanks
James



--
Message posted via http://www.officekb.com

Well it can be done, but it's not a very nice formula...

=MID(A1,LOOKUP(MATCH(TRUE,ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),SMALL((M ID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="
")*ROW(INDIRECT("1:"&LEN(A1))),ROW(INDIRECT("1:"&L EN(A1)))))+1,FIND("
",A1,MATCH(TRUE,ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0))-LOOKUP(MATCH(TRUE,ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),SMALL((M ID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="
")*ROW(INDIRECT("1:"&LEN(A1))),ROW(INDIRECT("1:"&L EN(A1)))))-1)

The formula Must be confirmed by holding down Ctrl and Shift and then
hit Enter.

Hope it can be of use
Ola Sandström


Attached zip-file:
http://www.excelforum.com/attachment...tid=3646&stc=1


+-------------------------------------------------------------------+
|Filename: Book5.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3646 |
+-------------------------------------------------------------------+

--
olasa
------------------------------------------------------------------------
olasa's Profile: http://www.excelforum.com/member.php...o&userid=17760
View this thread: http://www.excelforum.com/showthread...hreadid=390520

quite

you have to build a legend in order for excel to know the proper shortcut
for each name. make a list, one column is the the name (nokia) and on the
column on the right is the shortcut (nok)

after you do this, use this formula to make the new unified list

=CONCATENATE(VLOOKUP(F11,J11:K13,2,0)," ",G11)

obviously you have to make some changes for this formula to fit your
spreadsheet: F11 is a reference to the cell the name is in. G11 - the cell
the serial number's in. J11:K11 the range of cells the legend is in.

use the pull brush to copy this function to as much rows as there are on the
other lists. (this will work if the list you make is on the same sheet as the
other 2) if you want to move this list to another sheet or delete the
previous 2 without losing the resulting list, copy the new list and go to
edit - paste special: choose "values" and ok. then you're clear.

"themax16" wrote:


Afternoon everybody....

I have a pair of text based lists running down a worksheet.. i need to
create a 3rd list that is made up from the information in lists one and
two.

Eg.

List one List two List three (PLU)

Sony ericsson P900 SE P900
Nokia 6230 NOK 6230

As you can see the LPU is extracted from the Make and model.
Is it possible to set up some formulation for this?

Thanks
James


--
themax16
------------------------------------------------------------------------
themax16's Profile: http://www.excelforum.com/member.php...o&userid=23622
View this thread: http://www.excelforum.com/showthread...hreadid=390520

Make a table that list all the company names and expected notation and
sort it. It will look something like this:

Nokia NOK
Sony ericsson SE

Your third list will be:

=CONCATENATE(VLOOKUP(D1,$A$1:$B$2,2)," ",E1)

where D1 is your List one, E1 is your list two, $A$1:$B$2 is the
vlookup table.


Hope it helps.




themax16 Wrote:
Afternoon everybody....

I have a pair of text based lists running down a worksheet.. i need to
create a 3rd list that is made up from the information in lists one and
two.

Eg.

List one List two List three (PLU)

Sony ericsson P900 SE P900
Nokia 6230 NOK 6230

As you can see the LPU is extracted from the Make and model.
Is it possible to set up some formulation for this?

Thanks
James


--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=390520