none
Compare 5 columns with customer numbers and turnover numbers RRS feed

  • Question

  • Hi,

     

    From a SQL database i extract customer number, name and revenues numbers, for present year and for 5 years back.

    The list in excel looks like this;

    Customer number          Name                Year 1              Customer number        Name              Year 2  and the same for Year 1 through Year5

    12001                           Company_1      10 000 000      12001                          Company_1      9 000 000

    12002                           Company_2            10 000      12004                          Company_4       5 000 000

    12003                           Company_3      99 999 999     12005                           Company_5       4 000 000 

    So thats how the columns look like. Now when I get the data, the thing is that for some years there have not been any revenue on .e.g

    company number 12002, but there has been revenue in e.g Year1, so the list comes out only with customers that the company has had

    revenue on that particular year. So from the list above you see that it comes out uneven.

     

    Today I use IF formulas to check if the right customer number is aligned on the same row. So if customer number 12002 only has revenue

    in Year 1 and not in year2,3,4,and 5, I manually insert row so that customer number 12002 is the only number for e.g. row 2, so that 

    year2,3,4 and stands with zeros in the cells for the corresponding year. ( 1 row equals one customer number). 

    I there a way to code this in VBA so that it runs a check on the customer number and moves the rows so that 1 row corresponds to one

    customer number?

    Any help much appreciated, if somenone understood what I meant :)

     

     

    Wednesday, January 18, 2012 8:31 PM

Answers

  • hi,

    No VBA required, the easiest way, to my mind, would be to have all this information in one column, perhaps like this, & then do a pivot table on this table by customer number on the rows, Year along the columns & the value as the data field.

    Customer number          Name                Year             Value

    12001                           Company_1      Year 1          9 000 000

    12002                           Company_2     Year 1    5 000 000

    12003                           Company_3      year 4    4 000 000 

    12001                           Company_1      Year 2          3000 000

     

    • Marked as answer by EspenMB Friday, January 20, 2012 10:58 AM
    Wednesday, January 18, 2012 11:15 PM

All replies

  • hi,

    No VBA required, the easiest way, to my mind, would be to have all this information in one column, perhaps like this, & then do a pivot table on this table by customer number on the rows, Year along the columns & the value as the data field.

    Customer number          Name                Year             Value

    12001                           Company_1      Year 1          9 000 000

    12002                           Company_2     Year 1    5 000 000

    12003                           Company_3      year 4    4 000 000 

    12001                           Company_1      Year 2          3000 000

     

    • Marked as answer by EspenMB Friday, January 20, 2012 10:58 AM
    Wednesday, January 18, 2012 11:15 PM
  • Hi,

    Thanks for replying!

     

    My knowledge of pivot table's is a little slim, Im just used to IF, AND, OR, THEN and some simple VBA coding.

    The problem is that I paste those columns as explained in the first post from another table from a system I can only generate data per year.

    And the the report/excel sheet, for easy comparison has to be aligned like this:

    Cust.nr                Name                          2011               2010            2009            2008          2007

    12001                  Company 1               10 000              11 000          9 000           0                10 000

     

    But as explained in the first post I paste the data from the table I copied it from in like this, because I can only excract per year.

                                                 2011                                                         2010                                            2009

    12001         company_1          10 000          12001         company_1        11 0000     12001      company_1    9 0000 etc...

    12002         company_2                  0           12003        company_3         13 000       12003     company_3     10 000

     

     e.g. and for year 2010 then maybe customer 12002 doesnt have any revenue so it doesnt print out data for that year on that customer, so the next customer number with revenue comes under 12001 for year 2010, and so on. And for 2009 customer 12002 has revenue and gets printet som if I paste it in its misaligned.

    So I have to manually push customer 12003 down one row so only customer nr 12002 has its numbers on one row.

     

    So as you say, is it possible to do this with pivot tables? If so I'm gonna test it out.

    Thursday, January 19, 2012 4:48 PM
  • Hi,

     

    Thanks again, did it with pivot tables as you say, so everything is in order!

     

    Many thanks again!

    Friday, January 20, 2012 10:58 AM