Posts by Angela

    Re: Find The Lowest Date For Duplicate Id Nos.


    Hi Carlmack,


    Your solution worked very well and using the minimum option in the date area did exactly what I wanted to do. Thank you very much for all help. I learnt something useful from you.


    Hi Pangolin,


    I want to learn your method also but I did not know how to use the function you have mentioned in my dataset. Can you please use my data set as an example and let me know how to use it in that data set? Thank you very much.


    Angela

    Re: Find The Lowest Date For Duplicate Id Nos.


    Hi Carlmack, Pangolin


    I came to work this morning and pleased to see your suggestions for my question on ID Nos. and Date. I have to use the functions on my dataset and see how they work and will get back to you.


    Carlmack, there is one question I need clarification. I presume that you ask me to use the min option in the data area. Would that option also sum the ID Nos. if there are duplicates? Thanks.


    Thank you both very much for taking the time to help me.



    Angela

    Hi Excel Helpers,


    I am requesting your help in doing the following on my big data set. I am attaching a small sample data set and I need help to do the following. The first column contains the ID numbers and the second column has the dates. The rest of the columns are some data. When you look at the ID numbers, there will be some common ID Nos. numbers, for example, 300003 (4 in number) but the dates are different for them. What I am going to do is to create a pivot table with the ID Nos and the months. But I want the date to be only the oldest date for example, I want the date for 300003 to be 12/3/2004. I will be very thankful if someone can help me with this problem.


    Thanks,


    Angela

    Re: Convert Text to Numbers


    Marc,
    Wigi,


    Thank you both very much for your suggestions to my question. The ISERROR function worked well and I got the result I wanted. It is difficult to explain the project I am doing, but it did not matter if there are some test in col B. I will tackle those numbers in a different manner.


    Again I feel so good to say thank you to both of you for your time and effort.


    Regards,


    Angela

    Re: Convert Text to Numbers


    Wigi,


    I want to write codes because this is not the only thing I am doing in my spreadsheet. There are about 10 sheets or more in my spreadsheet and each sheet has a lot of different functions, like Vlookup, Match etc (these functions I can write the codes). This complicated data analysis takes a lot of time especially when I have deadlines to finish them. So I am trying to write VBA codes so that it will go from one sheet to another doing whatever needs to be done through codes. So I do not want to do one sheet manually if it is possible to incorporate codes. I thought someone out there must be knowing a lot more than me to help me. Otherwise, I will have to do this step manually.


    Thank you very much,


    Angela

    All Excel Helpers,


    I have a spreadsheet that contains numbers formatted as text in column A. Some of the numbers are prefixed with alphabets (e.g., they are either say, (1) 999118 or (2) M990695). The file I get every month does not have the same number of (1) or (2). What I am trying to in column B is to convert the numbers formatted as text in column A to numbers in column B using the formula (=1*A2). The formula works OK when they are not prefixed with alphabets. Otherwise, I get “#VALUE”. I want to write VBA codes to say that if column B is ‘#VALUE” in that cell, then copy column A into that cell. For example, B17 should now have what is in A17 which is M990695.
    I can do this manually but can it be done with VBA?


    A sample file is attached


    Thank you for all help


    Angela

    Re: VBA code to copy variable rows and columns


    Will, Robert


    Thank you both very much. Is there also a way in which I can code "last row" and "last column" (I can do that) and then instead of Range(A1:G10), use (Range(lasttow, lastcolumn))? How?


    Hope I am explaining well enough to understand.


    Thanks to anybody who can help.


    Angela

    Excel Helpers,


    Very often, I need to copy data from one sheet to another but I do not know the exact number of rows and columns (they vary with the files I get). Can anyone give me the codes that will copy number of unknown rows and columns?


    Thank you,

    Re: Convert manually written functions to VBA codes


    Badger101,


    Thanks for your suggestion. But I am curious to know why copy (ctrl+c) does not work for me. If I click ctrl+c, nothing happens. I do have the mouse pointer on the formula.


    Angela

    Re: Convert manually written functions to VBA codes


    Badger101,


    I do have the code from Aaron Blood and tried to use it. An input box pops up with the converted code but I do not know how to copy the code from the input box. Outside the input box, the mouse just hangs up. Copy and paste also does not work. Can you please tell me how you get the codes out of the input box?


    Thanks,


    Angela

    Dear Excel helpers,


    In my workbook, I have entered a lot of functions mainly VLOOKUPs. I want to write VBA codes for the functions without manually writing them. Is there a way in which I could get the manually written functions converted to codes automatically?


    Thank you,


    Angela

    Re: Code numbers and volume sharing


    Derk,


    Thank you so very much for the solution to my problem. This is exactly what I wanted to do using a bunch of formulas but I did not know what or how to use. I get some 40 to 50 numbers to do this and it is going to be very easy for me from now onwards.


    Again I appreciate your help very much and thank you for your time and efforts.



    Angela

    Dear Excel helpers,


    I have a data file with codes and data for the codes. Every month I receive a new file in which the following can happen.
    1. The old code and the new code do not change.
    2. The old code changes but there is only one new code replacing the old code
    3. The old code changes but can be replaced by two or three new codes that are different from the old code.


    Cases 1 and 2 do not give any problems because the data can directly be replaced.


    In case 3 what I need to do is to distribute the volumes between the new codes equally. If there are two new codes they will have half the volume each. If there are three new codes they will receive one-third of the volume each.


    I have attached a workbook with two sheets.
    Sheet 2 shows the old codes and Sheet 3 shows the old and the new codes with the volumes distributed between them. I did this manually but it is very cumbersome and takes a lot of time to do. Could it be done in an easier way? Can pivot tables help and if so, how can it be done with pivot tables.


    Thank you very much


    Angela

    Re: Remove duplicates based on Dates


    Badger101,


    The code is working very well now. I fixed the problem that was in the Item numbers. Your code saved me hours of my time and I could finish a lot of my work after removing the duplicates.


    Thank you so very much.


    Angela

    Re: Remove duplicates based on Dates


    Badger101,


    I tried your codes on my data sometime ago and it gives asn error at the line


    If Cells(i, 4) = Cells(i + 1, 4) Then Rows(i).EntireRow.Delete



    The error is Runtime Error "13"

    Type Mismatch


    Why is this happening?


    The Item numbers are formatted as Text. Is that a problem?


    There are 3701 rows and AN columns in my data.


    Thank you very much.


    Angela

    Re: Remove duplicates based on Dates


    Badger101,


    Thank you very much. I am attaching a small sample of my data showing only the Item number and Dates in the proper columns. There are duplicates in Item numbers but Dates are diffeent. I want to delete the Item numbers with the earlier Dates. The Item numbers are formatted as Text. Is that a problem? Also there are more than 4000 records in my file. Will sorting by Item numbers and then by Dates take a long time? should I use the option "Calculation Manual"?


    Thanks.