I have an excel file with different sheets. There are sheets for each month, starting at 1 (January) until 12 (December). In each of these sheet are entries for the specific month. There is a sum calculated. This is simply sum over all days of this month for this specific ID. The column sum is called „Summe“. In the file provided it will start at C2, so the header is in C2 and values are below. (It should be possible to let the user specify the exact column, in case it is needed, see attachments).
Task is now to (with VBA code):
1.) Extract the categorization:
The categorization (later on used) is determined by taking the last 4 digits from the key column. Add this to the sheet, after name column. Should be named "FASC". The values need to be displayed properly, so there should be "0000", and not "0" displayed. This could be done as pasting as text. (Or number with specific format to display the zeros.)
2.) Get the „Betten“ column
The column called „Betten“ should be given in the same file, however in another sheet. The name of the column should be assumed to be "Betten", otherwise the user has to specify the column where it can be found. The sheet where the basic data is located should be assumed to be the first sheet of the file. Otherwise the user has to specify the name of the sheet where the basic data is stored (if the first sheet oft he file is not possible to program in vba, then the user always has to specify the column manually).
The „Betten“ column needs to be added to the current month sheet. So let's assume it is september. Therefore the value from the column „Betten“ for each matching ID has to be copied and pasted in the the sheet 9. This needs to be done for matching ID. The IDs given in basic data have an additional prefix 70 and are given in text format. The ID in the corresponding month sheet, so for example month September is the sheet called 9, is given without the prefix and is displayed (I think) as text. These values were not entered as pure numbers, these are references from another sheet, check attachment. (But I think it is to be handle das text, not sure.?). The ID column is assumed to be named „RENR“ in the basic data sheet and in the corresponding month sheet, it is just assumed that it is in column A and the values start in A3.
3.) to insert an additional column before the sum column (after the categorization column created in step 1) which is named "Max" and calculated as follows:
Max = Betten*30/factor
„Betten“ is specific for each ID. The factor is a lookup into another file. This other file gives for each category a specific factor, so this is an easy lookup. This factor has to be used for the calculation. It is important to not limit the range, as I don’t know, how large this file will be, so I don’t know how many different factors will be in this list (how long the list will be).
If „Betten“ is missing for a specific ID then the calculations cannot be done and "Bettenangabe fehlt" should be entered. If factor is missing, "Richtwert fehlt" has to be put as text.
4.) mark those values in column sum as red, which are larger (or equal) than this max value. If „Betten“ is missing set background color yellow.
The final file should be stored as a new result file. However, there should be an option in the vba file (see attachment) where the user can enter „gefiltert“ or „komplett“ to have different options of output. In case of „komplett“ the complete file should be created. In case of „gefiltert“ there should be a filter applied on the column sum. Furthermore only the values marked as red (see step 4, so >=max) should be displayed (marked as red of course).
In regard to point 2.) there is one further thing:
Space is limited here and therefore I cannot enter more, please check attached description file along with the other files, in order to see the complete specification!
Dear Sir, I'm interested in your project. I'm willing to work before you hire me. I'll consider it a practice if you reject my work. Please pay me only after fully satisfied with my work. Thank You J Felix Bosco
39 freelancere byder i gennemsnit €178 på dette job
Hello, I would like to work on your project. I have great experience using excel macros / vba, data formatting and data manipulation in excel. I can start today if you like so. Regards.
Hi there My name is Carlos and I'm an expert in .Net and sql server. I have been working in software developement for the last 20 years. I have many experience in desktop and web development. Regards
Hi, I have understand the project requirement & interested to make and modify vba for this project. Please send a message so we can discuss more & start the project. Thanks.