ApplyMap wrong results

[#1]

Hi,

I use 2 maps to match the data I've got and the table below is the result.

Some ID2s match 2 different ID1s and therefore should match 2 different IDs, which works fine. However, the ID2 data (downloads) is assigned only to the first ID while the second one gets a value of 0, and I need both IDs to get the same value.

ID(4) = 4 and ID(5) = 4 instead of ID(4) = 4 and ID(5) = 0

I would really appreciate any help!

Ivelin

[Gysbert_Wassenaar]

Applymap works fine. You only have three ID2 values that have two entries in your mapping table. That's an error because it's impossible to determine which of the two values should be applied. Applymap uses only the first and ignores all others. You may not agree with that logic, but that's the way it works I'm afraid. You'll have to think up some way to make the ID2 values unique.

[#3]

That's really bad. There is no way I can make them unique.

Do you think there is another way around it?

[Gabriel]

Hi Ivelin,

I think in this case Map (ApplyMap) is wrong function to use.

Just think of ApplyMap as Vlookup in excel, VLookup a id and return first found value (meaning, if you have duplicate Salesperson and different SalesValue, Vlookup will pick up the first value and ignore the sceond). By default DISTINCT is applied.

I will think of another function that might help or you can thik of another function as well

Regards,

Gabriel

[swuehl · Accepted solution]

Try using JOINs.

Please check attached.

[Gabriel]

Hi,

Can I ask you a question?

Is the data coming from 2 tables?

If yes then, I will suggest using INTERVALMATCH() function.

This function accept and display duplicate.

Regards,

Gabriel

[#7]

Hi Gabriel,

thanks for the explanation above! Now I understand why Map doesn't work the way I want it.

As for the data, yes it comes from 2 (actually 3) sources. It looks the same as in 'data1' and 'data2' sheets in the excel file I've attached.

[#8]

Thanks swuelh!

I will try your solution out right away. I was thinking about JOIN as well but I remember reading some posts that don't advice on using it, I don't really know why. Are there any limitations or drawbacks in using it?

[Gabriel]

Hi,

Thanks for comment given above. Please mark the answer as Helpful.

Regards