Posts by RobertJ

    Hi Carim


    The problem is that I can have up to 1,000 names on the store sheets. One person (let's say Mr Smith) can have multiple entries on any (& possibly all 5 stores). So I need to add up all Mr. Simth entries from all Stores and subtract all his returns to get the liability figure.
    This is why I thought to go with vlookup or Index & Match on the liability sheet to calculate the above but I am getting nowhere.
    Appreciate any advice

    Hi Carim.
    My goal is to be able to show in the “Liability” sheet who has rented shirts, by tallying all the various shirts rented by any person across the 5 sheets (Store 1 through 5) and subtracting the shirts returned in the “Returns” sheet.
    If Mr. Smith has rented various sizes in any or all the stores and he did not return any or all of them, I need his name and number of un-returned shirts to appear in the “Liability” sheet.
    I tried with Vlookup but I am running in circles…

    Hi Carim,
    Understood. But the 3D formula would be static, wouldn't it? adding up let's say cell C1 across multiple sheets.
    What I am looking for is to tally how many shirts have been rented by one person across the five stores (five sheets) and then subtract the number of shirts returned by the same person. i.e Mr. Smith might have been in all five stores, might have been only in one. He might have rented one of the sizes or all of them. I was thinking to use vlookup but I am stuck... sorry.....any help is kindly appreciated.

    Good Day All


    I have a workbook with five sheets (each of them a rental store name) and a sixth one (return store).
    Each of the sheets is set up the same way: Column A - Date, Column B - Last Name, Column C- First Name, Column D - S shirt, Column E - M Shirt, Column F - L Shirt, Column G - Time of rental.
    The "Return Store" sheet is set-up the same but is fro returns.


    What would be the best way / formula to count the liability (number of shirts not returned) across the five stores if I set up a seventh sheet (Liability)? What I need is to see, if Mr. X has rented 2 shirts of each size in 2 stores but has returned only one of each, then how many shirts are still with Mr. X.


    Thank you in advance for your input.


    Take care.

    Re: Vlookup to subtract values across worksheets


    Hello Ali


    I am using Excel 2010.


    The 2nd formula does work but gave erroneous results. i.e. Donald would have 2 points (11 on 'Current Day Total' - 9 on 'Previous Day Total')


    Linda
    Hugh
    Bruce
    Leon
    Dawn
    Marcus
    Kathryn
    Mark
    Charles
    Maritza


    Thank you so much




    Rob

    Re: Vlookup to subtract values across worksheets


    Hello Ali.
    Unfortunately I am getting an invalid name error (#NAME?) with the formula.


    1. Are these names unique values? Yes
    2. Is there integrity across the sheets regarding the spelling of those names? Yes
    3. Will the position of the tables being referenced move at all? (Hopefully not!) No
    4. What is the calculation you want to perform? Is it current - previous or previous - current? current - previous

    Good Day,


    I have a workbook were I pull reports for a previous day and a running total for the current day on 2 separate sheets. I need to workout the difference in order to get today's figures on a third sheet. The names however are not in order on the 2 sheets. Would you use Vlookup formula and can you point me to the right syntax to work out the difference between the values corresponding to a particular name in the sheet "Current Day Total" & "Previous Day Total".


    Appreciate your help.


    Best regards


    Rob


    [TABLE="width: 379"]

    [tr]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [/TABLE]

    Re: VBA Code (or formulas) to remove duplicates?


    Indeed. But when I remove duplicates it does not have the option to chose which duplicates to remove. What I need is similar to conditional formatting, I need to remove the duplicates with the lower amount and keep the most recent with the highest amount.

    Re: VBA Code (or formulas) to remove duplicates?


    Quote from jindon;750348

    Upload a sample workbook with before/after clearly showing how you want it.


    Hi Jindon


    Here is the example. The customer below has 3 entries in sheet "Before". In sheet "After" I have sorted alphabetically and the customer has 2 entries for the same date & 1 entry for another date.
    What I would like to do is to automatically remove all "duplicates", basically keep the most recent, highest amount paid. In this case the entry showing $1,000 paid on Dec, 27 and delete the other 2.



    *Update - Sorry I am unable to upload the file. It could be my work station's permissions, but it halts after each attempt.

    Re: VBA Code (or formulas) to remove duplicates?


    Hi Jindon


    I have done so but what I need is to be able to show / keep only the highest amount paid most recently by a certain customer out off all the amounts paid by that customer.


    i.e. I have 500+ entries for last name being Smith but with different first names. Some of the Smith's have multiple entries and I need is to be able to chose the entry showing the highest amount paid most recently by each individual Smith.


    With sort by date & amount and then remove duplicates by col B&C I end up having only one Smith. I can use concatenate, or remove duplicates by customer ID which is unique but removing duplicates function does not allow to differentiate / chose which duplicates to remove. The issue is that the file is very large with over 20,000 entries.


    Thank you for your help.

    I have a large file with numerous duplicates. I would like to remove duplicates, however I need to differentiate which duplicates to remove.


    i.e. Linda Glover has 3 entries (some might have many more). I need to keep the entry that shows the highest amount paid but most recently and delete the other entries corresponding to her name.


    What is the most recommended (simplest) solution?
    [TABLE="width: 550"]
    [/TABLE]


    Appreciate your time and I thank you in advance

    [TABLE="width: 422"]

    [tr]


    [td]

    Customer ID

    [/td]


    [td]

    Last Name

    [/td]


    [TD="align: left"]First Name
    [/TD]

    [td]

    Date

    [/td]


    [td]

    Amount

    [/td]


    [/tr]


    [tr]


    [td]

    123456

    [/td]


    [td]

    Louzada

    [/td]


    [TD="align: left"]Carlos[/TD]

    [td]

    7-Dec-14

    [/td]


    [td]

    -

    [/td]


    [/tr]


    [tr]


    [td]

    44444

    [/td]


    [td]

    Spina

    [/td]


    [TD="align: left"]Anna[/TD]

    [td]

    7-Dec-14

    [/td]


    [td]

    -

    [/td]


    [/tr]


    [tr]


    [td]

    802307

    [/td]


    [td]

    Spina

    [/td]


    [TD="align: left"]Domingos[/TD]

    [td]

    7-Dec-14

    [/td]


    [td]

    -

    [/td]


    [/tr]


    [tr]


    [td]

    41807

    [/td]


    [td]

    McKnight

    [/td]


    [TD="align: left"]David[/TD]

    [td]

    14-Dec-14

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    45160

    [/td]


    [td]

    Genovese

    [/td]


    [TD="align: left"]Sherry[/TD]

    [td]

    14-Dec-14

    [/td]


    [td]

    $200.00

    [/td]


    [/tr]


    [tr]


    [td]

    619007

    [/td]


    [td]

    Glover

    [/td]


    [TD="align: left"]Linda[/TD]

    [td]

    15-Jan-14

    [/td]


    [td]

    $275.00

    [/td]


    [/tr]


    [tr]


    [td]

    619007

    [/td]


    [td]

    Glover

    [/td]


    [TD="align: left"]Linda[/TD]

    [td]

    31-May-14

    [/td]


    [td]

    $404.11

    [/td]


    [/tr]


    [tr]


    [td]

    619007

    [/td]


    [td]

    Glover

    [/td]


    [TD="align: left"]Linda[/TD]

    [td]

    11-Dec-14

    [/td]


    [td]

    $271.00

    [/td]


    [/tr]


    [tr]


    [td]

    738725

    [/td]


    [td]

    Brunkenhoefer

    [/td]


    [TD="align: left"]Robert[/TD]

    [td]

    19-Dec-14

    [/td]


    [td]

    $400.00

    [/td]


    [/tr]


    [tr]


    [td]

    38725

    [/td]


    [td]

    brunkenhoefer

    [/td]


    [TD="align: left"]Tracey[/TD]

    [td]

    19-Dec-14

    [/td]


    [td]

    $1,175.00

    [/td]


    [/tr]


    [tr]


    [td]

    8887902

    [/td]


    [td]

    Spina

    [/td]


    [TD="align: left"]Anna[/TD]

    [td]

    20-Dec-14

    [/td]


    [td]

    -

    [/td]


    [/tr]


    [tr]


    [td]

    8887904

    [/td]


    [td]

    Spina

    [/td]


    [TD="align: left"]Domingos[/TD]

    [td]

    20-Dec-14

    [/td]


    [/tr]


    [/TABLE]

    Good Day All,


    I have an Excel file with 5,000 names in column A and corresponding scores (points) in column B.
    What would be the easiest way to copy (enter) someone’s name from sheet one (Names) on to sheet two (Adjusted List) as many times as they have 50 points?

    i.e. If M. Smith, John has 4000 points, his name would be entered / copied 80 times. How can be this done elegantly, using VBA.

    Thank you for your help

    I have a workbook with 100+ names in column A. I would like to chose a random name and display it in a UserForm window. I am a little lost at how to do it. I did set up code to get a vba message box (plese see the file attached) but what I really need is to actually diplay the message in the list box when I press the 'Random Winner' button. Current code:

    Code
    Private Sub SelectWinner_Click()
    MsgBox Cells(Rnd * (Cells(Rows.Count, 1).End(xlUp).Row - 1) + 2, 1)
    End Sub

    Thanking you in advance.



    [/CODE]