Sort two excel lists to match up. 2

[ictoo]

Sorry if this is in the wrong place but this is all i could find.

My problem is I have two lists in excel one with 5000 products and another with 10000, what I want is to sort the 2nd list by the first so I get all the right products ID's matching up in the 5000 i have URL/descriptions and price. In the 10000 list i have alot of garbage data but 5000 of that data holds the category that the product is in and also the picture url.

What i want is to get excel to scan list two for all the id's in list two and then sort them next to each other so i can easily pick out all the data i want then instead of going through 10000 products can this be done?

Ive tried afew thing but they don't seem to work the way i want them.

thanks alot for your help, and saving my sanity.

Example
List 1

7000A-24
7000I-24
7000N-24
7000P-24
8000AA-1
8000AA-2
8000AA-3
8000AP
8000AP-3

List 2

35920_en_GB
35950_en_GB
4003_en_GB
40-200_en_GB
685_en_GB
686_en_GB
7000A-24_en_GB
7000I-24_en_GB
7000N-24_en_GB

I'd also like to get rid of the _en_GB i tried (trim _) but that started to cut the -24 off the end then.

 

[GlennUK]

To get rid of _en_GB why not do Edit/Replace on the list, replacing with null ( i.e. leave the Replace field as blank ).

As for sorting to get them to line up, don't you think it would be better to simply lookup the data from List 1 and return that alongside the List 2 entry? ( if I'm understanding you correctly )



Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[ictoo]

Find/Replace.. why didn't i think of that thanks a lot that's sorted.

But I tried a lookup and i'm not sure if i'm using it right how do i make it so they line up and the data in the other rows then line up. so i can then just copy all the image url from the 10000 list to 5000 list.

 

[ictoo]

Ok I was using lookup and vlookup sort of wrong, but I think i've got it to work now i've used.

=VLOOKUP(C2,$A$2:$B$9500,2,FALSE)

and I think it's working it looks like it has still sceptic.. as always but thanks for poking me back in the right direction Glenn.

 

[SkipVought]

Be VERY CAREFUL with the values that remain that APPEAR to be pure NUMBERS, like...
[tt]
35920_en_GB
[/tt]
resulting in
[tt]
35920
[/tt]
which is TEXT, a string of numeric characters, rather than a number. And that's what it must be. HOWEVER, if you edit the cell where that value resides, and simpy hit ENTER, Excel CONVERTS your TEXT to a NUMBER. If that were to happen, your lookup will not work using this converted value.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[lionelhill]

To remove the _en_GB you could also use
=left(A2, len(A2)-6)
If not all lines end in _en_GB, provided there aren't too many alternatives, you could use an IF to check. For example, if some lines are already as they should be
=if(right(A2, 6)="_en_GB", left(A2, len(A2)-6), A2)
You would then use this trimmed column in your look-up. This also deals with Skip's very valid concern, because the output of "left" is still text, even if it looks like a number. Of course if the corresponding entry in the other table is a genuine number, you're doomed.

 

[SkipVought]

Of course if the corresponding entry in the other table is a genuine number, you're doomed.

If this is the case in your LOOKUP table, that is the lookup column of data being a mixture of TEXT and NUMBERS, you must CONVERT the NUMBERS to TEXT. This can NOT simply be done by changing the number format to TEXT.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[lionelhill]

yes, that's exactly what I meant. In the table where everything ends _en_GB we know that everything is initially text. In this table, the danger is that after we've trimmed it, any further manual edits will change a text-that-looks-like-number into a number.

My concern was the table where things don't end _en_GB, where it's quite possible some entries are already values rather than strings. This being the case, if we trim a text so it remains a text, and search against a column containing values, we won't get a match.

 

[GlennUK]

If the lookup list is the one with the _en_GB endings, then just append it onto the lookup values, like:

=VLOOKUP(C2&"_en_GB",$A$2:$B$9500,2,FALSE)

You are then guaranteed a text to text match.


Cheers, Glenn.

Beauty is in the eye of the beerholder.