Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: Excel Help please

  1. #1
    Full Member greghsa's Avatar
    Join Date
    Aug 2010
    Location
    Johannesburg
    Posts
    57
    Thanks
    19
    Thanked 13 Times in 11 Posts

    Excel Help please

    Excel fundi's please help.
    I have an Excel pricelist worksheet (.xls Excel 2003, 300 lines) with the following columns:
    Code Price1 Price2 Price3
    I also have another worksheet(.xls Excel 2003, 2000 lines) with the following columns:
    Code Description

    I want to modify first worksheet or create a third worksheet that will have:
    Code Description Price1 Price2 Price3

    Copy and paste or sorting will not work as different number of lines.
    All codes in first worksheet are in second worksheet.

    I would like excel to look for same code in different worksheet and pull the description .
    Hope this makes sense. Any ideas would be appreciated

  2. #2
    Diamond Member adrianh's Avatar
    Join Date
    Mar 2010
    Location
    Cape Town
    Posts
    5,089
    Thanks
    336
    Thanked 808 Times in 642 Posts

    Talking

    Quote Originally Posted by greghsa View Post
    Excel fundi's please help.
    I have an Excel pricelist worksheet (.xls Excel 2003, 300 lines) with the following columns:
    Code Price1 Price2 Price3
    I also have another worksheet(.xls Excel 2003, 2000 lines) with the following columns:
    Code Description

    I want to modify first worksheet or create a third worksheet that will have:
    Code Description Price1 Price2 Price3

    Copy and paste or sorting will not work as different number of lines.
    All codes in first worksheet are in second worksheet.

    I would like excel to look for same code in different worksheet and pull the description .
    Hope this makes sense. Any ideas would be appreciated
    Ok, here is a demo

    Code:
    Set up Book1 Sheet 1 as follows: (Save it and keep it open)
    
    code	price1	price2	price3
    code1	price1_1	price2_1	price3_1
    code2	price1_2	price2_2	price3_2
    code3	price1_3	price2_3	price3_3
    code4	price1_4	price2_4	price3_4
    
    Set up Book2 Sheet 1 as follows: (Save it and keep it open)
    
    code	descr
    code1	descr1
    code2	descr2
    code3	descr3
    code4	descr4
    
    Set up Book3 Sheet 1 as follows:
    
    code                    desr	                                           price1	                 price2	                         price3
    =+[Book1.xls]Sheet1!A2 	=VLOOKUP(A2,[Book2.xls]Sheet1!$A$2:$B$5,2,FALSE)   =+[Book1.xls]Sheet1!B2       =+[Book1.xls]Sheet1!C2         =+[Book1.xls]Sheet1!D2
    =+[Book1.xls]Sheet1!A3 	=VLOOKUP(A3,[Book2.xls]Sheet1!$A$2:$B$5,2,FALSE)   =+[Book1.xls]Sheet1!B3       =+[Book1.xls]Sheet1!C3         =+[Book1.xls]Sheet1!D3
    =+[Book1.xls]Sheet1!A4 	=VLOOKUP(A4,[Book2.xls]Sheet1!$A$2:$B$5,2,FALSE)   =+[Book1.xls]Sheet1!B4       =+[Book1.xls]Sheet1!C4         =+[Book1.xls]Sheet1!D4
    =+[Book1.xls]Sheet1!A5 	=VLOOKUP(A5,[Book2.xls]Sheet1!$A$2:$B$5,2,FALSE)   =+[Book1.xls]Sheet1!B5       =+[Book1.xls]Sheet1!C5         =+[Book1.xls]Sheet1!D5
    
    Just substitute your book & sheet names, then correct your columns, rows & cell range for the vlookup.
    
    Look carefully at the cell range, return column and exact match specifyer in the vlookup.
    
    Cell range is top left where code starts and right bottom where description ends.
    Return column is 2 because column 1 is the code to be looked up and column 2 is the description to be returned.
    Exact match is set to FALSE otherwise it will return the nearest match which may not make sense. It is better to be able to convert a missing description to "MISSING DESCRIPTION" rather than a incorrect value
    
    You can set up the missing description check as follows:
    
    =IF(ISNA(VLOOKUP(A6,[Book2.xls]Sheet1!$A$2:$B$6,2,FALSE)),"MISSING DESCRIPTION",VLOOKUP(A6,[Book2.xls]Sheet1!$A$2:$B$6,2,FALSE))
    
    Remeber to correct all the cell references
    ...and there you go
    Last edited by adrianh; 14-Aug-10 at 11:09 PM.
    How easily someone is offended is directly proportional to how stupid they are.
    ~GS Elevator

  3. Thanks given for this post:

    greghsa (14-Aug-10)

  4. #3
    Full Member greghsa's Avatar
    Join Date
    Aug 2010
    Location
    Johannesburg
    Posts
    57
    Thanks
    19
    Thanked 13 Times in 11 Posts
    Quote Originally Posted by adrianh View Post
    Ok, here is a demo

    Code:
    Set up Book1 Sheet 1 as follows: (Save it and keep it open)
    
    code	price1	price2	price3
    code1	price1_1	price2_1	price3_1
    code2	price1_2	price2_2	price3_2
    code3	price1_3	price2_3	price3_3
    code4	price1_4	price2_4	price3_4
    
    Set up Book2 Sheet 1 as follows: (Save it and keep it open)
    
    code	descr
    code1	descr1
    code2	descr2
    code3	descr3
    code4	descr4
    
    Set up Book3 Sheet 1 as follows:
    
    code                    desr	                                           price1	                 price2	                         price3
    =+[Book1.xls]Sheet1!A2 	=VLOOKUP(A2,[Book2.xls]Sheet1!$A$2:$B$5,2,FALSE)   =+[Book1.xls]Sheet1!B2       =+[Book1.xls]Sheet1!C2         =+[Book1.xls]Sheet1!D2
    =+[Book1.xls]Sheet1!A3 	=VLOOKUP(A3,[Book2.xls]Sheet1!$A$2:$B$5,2,FALSE)   =+[Book1.xls]Sheet1!B3       =+[Book1.xls]Sheet1!C3         =+[Book1.xls]Sheet1!D3
    =+[Book1.xls]Sheet1!A4 	=VLOOKUP(A4,[Book2.xls]Sheet1!$A$2:$B$5,2,FALSE)   =+[Book1.xls]Sheet1!B4       =+[Book1.xls]Sheet1!C4         =+[Book1.xls]Sheet1!D4
    =+[Book1.xls]Sheet1!A5 	=VLOOKUP(A5,[Book2.xls]Sheet1!$A$2:$B$5,2,FALSE)   =+[Book1.xls]Sheet1!B5       =+[Book1.xls]Sheet1!C5         =+[Book1.xls]Sheet1!D5
    
    Just substitute your book & sheet names, then correct your columns, rows & cell range for the vlookup.
    
    Look carefully at the cell range, return column and exact match specifyer in the vlookup.
    
    Cell range is top left where code starts and right bottom where description ends.
    Return column is 2 because column 1 is the code to be looked up and column 2 is the description to be returned.
    Exact match is set to FALSE otherwise it will return the nearest match which may not make sense. It is better to be able to convert a missing description to "MISSING DESCRIPTION" rather than a incorrect value
    
    You can set up the missing description check as follows:
    
    =IF(ISNA(VLOOKUP(A6,[Book2.xls]Sheet1!$A$2:$B$6,2,FALSE)),"MISSING DESCRIPTION",VLOOKUP(A6,[Book2.xls]Sheet1!$A$2:$B$6,2,FALSE))
    
    Remeber to correct all the cell references
    ...and there you go
    I new it would have a VLOOKUP in it, but would never have cracked it myself. Thank You

  5. #4
    New Member
    Join Date
    Sep 2012
    Location
    Cape Town
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Guys

    Have a question on excel:

    I have a created a table on excel with coloumns and rows.

    I effectively need to find the sum of a criteria that is required in Coloumn A and row A ie

    A B C D E F G H

    1
    2
    3
    4
    5
    6
    7

  6. #5
    Diamond Member adrianh's Avatar
    Join Date
    Mar 2010
    Location
    Cape Town
    Posts
    5,089
    Thanks
    336
    Thanked 808 Times in 642 Posts
    You need to explain what you need far more clearly than that...either that or I am to doff to figure out what you are on about.
    How easily someone is offended is directly proportional to how stupid they are.
    ~GS Elevator

  7. #6
    New Member
    Join Date
    Sep 2012
    Location
    Cape Town
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi, Apologises the thread uploaded before I finished the Q:

    I have an array of data. In the top row , row 1, I have the following consistent cell descriptions

    T1Actual T1Budget T1Prior T1Actual T1Budget T1Prior

    In the first coloumn, col A i have the following data


    A8


    A3


    A8
    A8

    A6

    What i am trying to calculate is the sum of all the points in the table where T1Actual and A8 intersect.
    The 3 parts of the formulae are effectively
    Table A1: Z50
    Criteria 1 : Row1, "T1Actual"
    Criteria 2 : Coloum A, "A8"

    Now sum where all these points meet in the table.

    Hope this make more sense

    Thanks

  8. #7
    Diamond Member adrianh's Avatar
    Join Date
    Mar 2010
    Location
    Cape Town
    Posts
    5,089
    Thanks
    336
    Thanked 808 Times in 642 Posts
    Why don't you just sort all the data by t1actual. Doing so will group all the A8's together making it easy to sum.
    How easily someone is offended is directly proportional to how stupid they are.
    ~GS Elevator

  9. #8
    New Member
    Join Date
    Sep 2012
    Location
    Cape Town
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    each 3 refrences refers to a month in 2012 ie Jan then Fe then march etc etc


    T1Actual T1Budget T1Prior T1Actual T1Budget T1Prior T1Actual T1Budget



    Jan 12 Jan 11 Feb 12 Feb 11 Mar 12
    T1 T1 T1 T1 T1 T1 T1 T1

  10. #9
    Diamond Member adrianh's Avatar
    Join Date
    Mar 2010
    Location
    Cape Town
    Posts
    5,089
    Thanks
    336
    Thanked 808 Times in 642 Posts
    Look, there is another way of doing it. What you would do is to write a little subroutine in VBscript that will run through your data collecting and summing the figures that you need.

    I find it very difficult to picture your data so its hard to write the program.

    PM the file or a sample set of the data to me and I'll show you how to do it.
    How easily someone is offended is directly proportional to how stupid they are.
    ~GS Elevator

  11. #10
    New Member
    Join Date
    Sep 2012
    Location
    Cape Town
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    See attached example


Page 1 of 2 12 LastLast

Similar Threads

  1. Pastel Partner to Excel Auto mapping
    By tyrone k in forum Technology Forum
    Replies: 2
    Last Post: 11-May-09, 04:59 PM
  2. [Article] Nifty Excel spread sheets
    By IanF in forum General Business Forum
    Replies: 7
    Last Post: 05-Dec-08, 12:14 PM
  3. SASOL & EXCEL in trouble
    By Sieg in forum General Business Forum
    Replies: 5
    Last Post: 21-Oct-08, 11:17 AM

Tags for this Thread

Did you like this article? Share it with your favourite social network.

Did you like this article? Share it with your favourite social network.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •