Technical Discussion
  >> Windows Issues


Register (or login) on our website and you will not see this ad.


  Print Thread
Standard User gunnersboy
(fountain of knowledge) Tue 10-Nov-15 15:23:44
Print Post

Excel Help


[link to this post]
 
Hopefully the place to ask. I've asked a few Excel questions in the past and people here have come good so I'm hoping for the same.

A brief overview - I have reports that generate the data and what the reports generate I cannot edit.

So, I have a excel sheet which has data of account numbers active in the last 6 months along with names and other information running from column A to H. Column A is account number, B is Name. Let's call this "SHEET A". On another sheet, "SHEET B" is another generated report with the same two columns above. This contains account numbers of all customers, even if they haven't been active in the last 6 months. "SHEET B" also contains the amount of times this customer has been active, displayed in column C and how much they've spent, displayed in column D. I need to create an excel list which compares the two lists and creates a list of ONLY the customers that have been active in the last 6 months (SHEET A data) and add in how much they've spent and how many times. SHEET B data. I had done this formula for every row...:

=IF(ISNA(VLOOKUP(A2,'SHEET B'!A:D,2,FALSE)),"",VLOOKUP(A2,'SHEET B'!A:D,2,FALSE))

This displays the correct information but where this information doesn't match up (the non active customers) there is an empty row. I need to find out the top 10/20/30 customers with their account number and other information and create a separate excel list. To make things slightly more complicated. The report doesn't accumulate all the visits within a month. For example, if they visit on the 1st Oct it will log this on one row, and if they visit again on the 3rd Oct it will list it again rather than saying 2 visits.

Long and complicated but I'm hoping someone can 1) Understand and 2) Help.

Looking forward to the replies....

Virgin Media:120Mb/s

Download Speed: 123.8 Mb/s
Upload Speed: 10.0 Mb/s
My Broadband Speed Test
Standard User camieabz
(sensei) Wed 11-Nov-15 03:12:21
Print Post

Re: Excel Help


[re: gunnersboy] [link to this post]
 
A combination of SUMIF or COUNTIF for generating totals for customers and LARGE for finding the 'n' largest values might be one option.
  Print Thread

Jump to