Re: Find The Lowest Date For Duplicate Id Nos.
Hi Pangolin,
I saw your solution only today five days after you posted it. Thank you very much for helping me with a alternate solution. It worked well on my data set.
Angela
Re: Find The Lowest Date For Duplicate Id Nos.
Hi Pangolin,
I saw your solution only today five days after you posted it. Thank you very much for helping me with a alternate solution. It worked well on my data set.
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,
It works. Thank you very much
Angela
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 so very much. I will try the codes and get back to you is there is a problem. This is going to help save a lot of time.
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.
Re: Remove duplicates based on Dates
Badger101,
Thank you very much for your prompt reply. Can you write to me the codes also to delete rows if possible? I would appreciate your help very much.
Angela