VLOOKUP Help

[gbfoligno]

Hello everyone!

I’m trying to create a formula so I can incorporate at this macro I’m working on. I need a “different” VLOOKUP. This excel file contains two worksheets, the first one is “August” and the other one is the “Control Number Table”.

August - Contains a list of Customer Numbers.

Control Number Table - Shows all control numbers available for Costumer Numbers.

Each Customer Number has a Agency Code and Cost Center. The condition for this VLOOKUP is:

If Customer Number, Agency Code and Cost Center repeat I need the same control number. But sometimes the same Customer Number comes with different Agency Code and Cost Center, in this case I need the next Control Number available (please see the example on excel file attached so you can understand better).

Any ideas?

[Kalithro]

I don't know about other users... but I tend to not want to open .xls files esp... ones with names 31029104 from someone with 5 posts. Just saying.

[gbfoligno]

Hi Kalithro,

I'm sorry but that's how I receive this report at work. I understand what you mean but I've never thought about it...

If you don't belive me just do a search for previous posts

Like this one...
http://www.excelforum.com/excel-prog...31#post3367331

[Sam Capricci]

Ok, looking at your file, I must say I really am not sure from your "example of how the results should be" how you would arrive at them from the given info. For example cust # 3729318 on the August sheet has the same agency code and cost center #. On the control number table it occurs many times and the control numbers vary. Can you explain further?

Edit: and you have that number multiple times in your "results" section with the same control #, Agency code and cost center for at least 5 by my count. So I need to know how you would arrive at your results.

[Kalithro]

I'm not a 100% sure what your looking for either. You have defined codes for customer numbers, but you want to make new ones? Well here is a way to make new control numbers based on the 3 conditions. Rule, Key has to be generated and then the Key has to be sorted on.

Warning! Adding new Keys each day or week or month then sorting will reorder the control numbers.

I wish I knew more on what you needed.

[gbfoligno]

Ok, looking at your file, I must say I really am not sure from your "example of how the results should be" how you would arrive at them from the given info. For example cust # 3729318 on the August sheet has the same agency code and cost center #. On the control number table it occurs many times and the control numbers vary. Can you explain further?

Edit: and you have that number multiple times in your "results" section with the same control #, Agency code and cost center for at least 5 by my count. So I need to know how you would arrive at your results.

I'm not a 100% sure what your looking for either. You have defined codes for customer numbers, but you want to make new ones? Well here is a way to make new control numbers based on the 3 conditions. Rule, Key has to be generated and then the Key has to be sorted on.

Warning! Adding new Keys each day or week or month then sorting will reorder the control numbers.

I wish I knew more on what you needed.

Let me explain better,

“4533264.xls” is a list of a client request. This 4533264 code represents my client. Everything he is buying in august is there. The first worksheet - August - is the client request.

Each client has different customer numbers, they represent the address where our client wants to receive his product. For example, 3729318 could be an address like 4th street and 3734988 57th street.

If customer number repeat means this client is receiving more than 1 product in this address, if you check customer number 3729318 you’ll see he is receiving 7 products and 3734988 – 4 products.

Agency Code + Cost Center it is a type/model of the product. For example – Check customer number 3729318 - he’s buying 7 products and 6 of them are the same type/model (44105 0000EUC44105001) only one different (44105 2P4416401702EUC).

Also check customer number 3734988 he’s buying 4 products, all of them are same type/model (42700 00000DHSDFCSEUC)

Here comes the most boring part of my job (that’s why I’m creating this macro). I will not go into much detail about the process of my work is very complicated.

For each purchase order I receive I got to pick one control number available for the respective costumer number. For example 3729318 Customer Number

7 Purchase Orders – 7 products – 6 of them are equals, the same type/model, only one different. So I need 7 control numbers available.

I check them at control number table. If you go there and use filter function to check control numbers available for customer number 3729318 you’ll see 1,2,3, 4, 5,… 0A,0B, 0C, 0D….

Condition to pick control number - Cust# 3729318 for example;

7 Purchase Orders - 7 products – 6 of them are equals, the same type/model, only one different.

In this case I need two control numbers, it’s one for quantity of type/models per customer number. For type/model 44105 0000EUC44105001 control number should be 1 and for type model 44105 2P4416401702EUC control number should be 2.

Now 3734988 Cust#;

4 Purchase Orders – 4 products – all of them are equals, the same type/model (42700 00000DHSDFCSEUC), so in this case I need only one control number. It should be control number 1.

Follow new attached file to understand better.

Thank you both!

[Kalithro]

Well I likely just wasted my time... What version of Excel must this work for? I have a working 2007 and greater version. Are the control numbers tied to specific Customer Numbers? Or do Control Numbers just count 1-99 A0-ZZ for all Customer Numbers?

If the Control Numbers are not tied to Customer Numbers (far as I can tell they are not), then the Control Number Table isn't needed and a generic table can be used. 1=1, 100 = 0A, 101 = 0B, etc...

Well you can try this and let me know if it works. If using Control Numbers and Customer Numbers Table (doesn't look like you have to), you have to follow 2 rules... If using the Generic, follow one rule. The offset column will count 1-1000000 not 1-99 to 0A-ZZ. Rule, have to sort by Key. Maybe Rule (number 2- if using Customer Control numbers lookup table), have to sort by Customer Number). Workbook is already sorted.

Let me know how it works in Excel 2003, if that's what your using (may not work).

[gbfoligno]

Well I likely just wasted my time... What version of Excel must this work for? I have a working 2007 and greater version. Are the control numbers tied to specific Customer Numbers? Or do Control Numbers just count 1-99 A0-ZZ for all Customer Numbers?

If the Control Numbers are not tied to Customer Numbers (far as I can tell they are not), then the Control Number Table isn't needed and a generic table can be used. 1=1, 100 = 0A, 101 = 0B, etc...

Well you can try this and let me know if it works. If using Control Numbers and Customer Numbers Table (doesn't look like you have to), you have to follow 2 rules... If using the Generic, follow one rule. The offset column will count 1-1000000 not 1-99 to 0A-ZZ. Rule, have to sort by Key. Maybe Rule (number 2- if using Customer Control numbers lookup table), have to sort by Customer Number). Workbook is already sorted.

Let me know how it works in Excel 2003, if that's what your using (may not work).

I'm using 2007 version.

That's the point, Control Number Table is very important. For example;

Once Control Numbers 1 and 2 were used for Customer Number 3729318 in August report we'll never use it again.

In November I'll receive a new client request, and also Control Number Table updated. If I receive a new request for customer number 3729318, the first control number available will be 3.

[Kalithro]

Do you have any freedom to change control numbers to something that is more unique? Are the 1-99 0A-ZZ numbers set in stone?

If the control numbers can be more unique... Try something like this. I was just playing around some more with this... To get exactly what you want; someone will likely have to write some VBA for you.

[FDibbins]

This is probably not what you wanted, but give it a try...

With a seed if 1 in G4, use this in G5, copied down (I actually put it in J5, copied down, so you vcan see your original numbers) ....
=IF(AND(F5=F4,H5=H4,I5=I4),J4,J4+1)