vlookup in excel 2007

Cust id Name sugar  pen pencil milk
AF15A1       Siddappa 1 - 1 -
AF8A1        Hanumamma 1 1 - 4
AG7A1        Chinnappa 10 1 3 1
AF6A1        Sanna Virupanna - - - 20
AF2A1        Hanumantappa 2 1 - 3
AD34A1       Amarappa - - 1 -
AF14A1       Mariyappa 1   - 1
AF43A1       Venkatesh 2 1 9 -

 

hi, my name is suresh, with the above example ii need to know, which cust id has purchased what and how many times with the help of vlook up, the above example is of only 5 columns and 10 rows with the detials in it, the columns may go upto 150 depending on the item and the rows can go upto 300 depending upon the cust id`s

 

 

suresh

Almir's picture

Pivot table is what you need

Use pivot table to summarize data. If you need additional help, let me know.

Nick's picture

VLOOKUP

hi Suresh

Please show me an example of the output you want.

tks

Nick

 

 

vlookup

hi nick, thks for ur reply, i am finding it little bit difficult to explain, what exactly is, but still i have managed to give you , hope it will be sufficient,

if u don`t understand the below,i can call u on the skype id if u have one. so that i can explian u clearly what i need.

  month 2        
Cust id Name sugar  pen pencil milk
AF15A1 Siddappa 4 1 1 1
AF8A1 Hanumamma 0 0 1 0
AG7A1 Chinnappa 1 0 0 2
AF6A1 Sanna Virupanna 1 2 1 2
AF2A1 Hanumantappa 1 4 10 1
AD34A1 Amarappa 1 3 2 1
AF14A1 Mariyappa 3 - - 2
AF43A1 Venkatesh - 2 3 1

the first mail was for month 1 and the above is for month 2, and the out come as below,

  output          
Cust id Name sugar  pen pencil milk total visits
AF15A1 Siddappa 5 1 2 1 9
AF8A1 Hanumamma 1 1 1 4 7
AG7A1 Chinnappa 11 1 3 3 18
AF6A1 Sanna Virupanna - - - 22 22
AF2A1 Hanumantappa 3 5 - 4 12
AD34A1 Amarappa - - 3 - 3
AF14A1 Mariyappa 4 - - 3 7
AF43A1 Venkatesh 2 3 12 1 18

the cummulaitve of both the months example cust id AF15A1 has brought sugar on 5 visits(i.e, once in month 1 and 4 times in month 2), so need this in vlook up, as i maintain the data month vise, its difficult to enter manually every month, is it possible to have one excel sheet for all the month and then i can do a vlookup cummulative for all the months to find who is purchased/visited what and how mant times.

 

suresh

 

 

 

 

Nick's picture

VLOOKUP

I still don't see how it's possible to derive the output from your input...

anyway... I think your best bet is to add a column with the month, and then use a pivot table to summarise the totals.

Detailed instructions on a very similar problem here

Nick