+ Reply to Thread
Results 1 to 24 of 24

Matching/Adding

  1. #1
    Registered User
    Join Date
    08-06-2006
    Posts
    36

    Matching/Adding

    I'm not really sure what I need to do this but I would appreciate any help or for someone to point me in the right direction.

    I'm trying to get excel to add up some numbers from multiple worksheets, as they are created, to another master worksheet. The first worksheet will become the master worksheet and will take the numbers from subsequent worksheets that become available each week. The catch is that I need the master sheet to take the numbers from the rows with corresponding names and add them and then add any new names to the list.

    Here is an example:

    Worksheet A(master worksheet that will accumulate the numbers from other sheets as they become available) will have the same columns of data as the other sheets; columns B,C,D,E,F. Columns B,C have name data. Columns D,E,F have the numbers that I need added.

    Worksheet B will be the second worksheet that becomes available the following week. It will look very similar. It will have most of the same names and their data for that week but there could be more new names or less names but not necessarily the same names.

    What I need excel to do is recognize the same names that occur in worksheet A and B and then add worksheet B's numbers to the corresponding row in worksheet A. I then need to add the new names that come up that week to worksheet A's unused rows at the bottom.

    If cell B4(on worksheet A) matches with a name in B14(on worksheet B) I need worksheet B's numbers from D14,E14, and F14 to add into worksheet A's D4,E4, and F4.

    *I can copy and paste worksheet B's data next to the data in worksheet A to complete the task if necessary.

    I apologize for such a lengthy post. Let me know of any questions to things unclear. Thank you for any help!

  2. #2
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591
    Hi

    My thought would be to have a first sheet that is the master sheet that cumulates all subsequent sheets, and the second and subsequent sheets are the data sheets. Using that approach and with the master sheet called sheet1, try the following

    Please Login or Register  to view this content.
    This will delete any existing data on sheet1 every time it is run, and rebuild completely. You could put it into a sheet activate event for sheet1, or attach to a button, or run manually.

    HTH

    rylo

  3. #3
    Registered User
    Join Date
    08-06-2006
    Posts
    36
    Hi, thank you for your response.

    I have ZERO experience with any programming features in Excel. What exactly do I do with the code you posted?

    Sorry for being such a beginner with this stuff; would greatly appreciate your guidance. Thank you!

  4. #4
    Registered User
    Join Date
    08-06-2006
    Posts
    36
    Sorry, I figured out how to run a macro. First time I opened Visual Basic. LOL

    The code looked like it almost worked except it seemed to have cut off the top row or moved everything up one row. I just needed the data to be taken from and added to starting in row 4.

    Also, I probably should have mentioned I had some other simple calculations in columns A,G,H,I that take data from D,E,F that you programmed.

    Column A ranks the list like this(taken from first row of calculated data): =RANK(I4,$I$4:$I$1000)+SUMPRODUCT(--($I$4:$I$1000=I4),--($D$4:$D$1000=D4),--($F$4:$F$1000>F4))

    (all from first row)
    Column G adds D4+E4

    Column H is =$D$4-D4

    Column I is =(D4/(D4+E4))

    I'm not sure if this helps or not since I'm not really sure how the programming works but your help is very much appreciated!

  5. #5
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591
    Hi

    Can you create a sample workbook that we can use to test. Include the extra calculations that you have, the formats, data starting rows etc.

    Also, can you provide a sample output of what the result should be for some (or all) of the data you provide.


    Ta

    rylo

  6. #6
    Registered User
    Join Date
    08-06-2006
    Posts
    36
    It says the files are invalid when trying to upload.

  7. #7
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591
    Hi

    Have you zipped them?


    rylo

  8. #8
    Registered User
    Join Date
    08-06-2006
    Posts
    36
    Good call on the zip. Alright, the first sheet is what the results should look like. The two other sheets are samples of two weeks of data.

    The data is sorted by column A in ascending order. I tweaked some formulas on the master sheet(Rankings) in columns A,G,H,I but it should be good to go now. Also, column I's formula should be copied all the way down with the others but it messes up the rankings in column A because of #DIV/0 so I'll have to fill down when they get filled up.

    Columns B and C are name data but only B needs to account for the matches on all sheets. It shouldn't matter what is in column C, if that makes a difference.

    Let me know if there is something else needed.
    Attached Files Attached Files

  9. #9
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591
    Hi

    OK try the following code

    Please Login or Register  to view this content.
    This is set up for your current structure and assumes that you already have some data in the output file.

    rylo

  10. #10
    Registered User
    Join Date
    08-06-2006
    Posts
    36
    Everything seems to work great! And thank you for putting in the sorting bit at the end.

    This is a huge help and greatly appreciated. Thanks so much rylo!

  11. #11
    Registered User
    Join Date
    08-06-2006
    Posts
    36
    rylo, thank you for your help on this a few months ago. I started using the macro you sent me on spreadsheets with real data and ran into a minor problem.

    Everything is calculating ok except I would like to know if the name matching can disregard whether names match the case of the letters. If there is one letter case that's different, it treats it as a new entry. Can the program be made to disregard letter case?

    Thanks again
    Attached Files Attached Files

  12. #12
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591
    Hi

    Try this. I've made all the name upper case to overcome the problem. If you don't like upper case, then you could use lower, or proper.

    I've also sped things up a bit by turning off / on the calculation, and I've also fiddled the formulas in column I do that it only has formulas for the used range.


    rylo

    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    08-06-2006
    Posts
    36
    Alright, I tested the macro out and it seems to work much better. I went ahead and used lowercase for all the names. Thank you for fixing column I, that will help out a lot.

    When I initially ran the first macro that you created, I was under the impression that it would only look for matching names in column B but it appears that it is looking for matches in column B and C. Can the macro just look for matching names in column B and then use the latest week's column C entry for that name and use that? Otherwise, it creates a new entry for the same name.

    Sorry for the nitpicking
    Last edited by Bullfn33; 09-10-2007 at 04:32 PM.

  14. #14
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591
    Hi

    Your original post advised that the name data was in columns B and C
    Columns B,C have name data.
    so I used both those columns to determine the name.

    If you now only want to use column B as the name, that's OK, but I'm not sure how you want to handle the column C data. What happens if there is a name in earlier weeks that is not in the latest week? How is the column C data to be used?

    Can you give me some examples from the file, and how you would expect to see them processed.

    rylo

  15. #15
    Registered User
    Join Date
    08-06-2006
    Posts
    36
    From the example I posted: In week 1, the top name on the list is jrjohnson1500 in column B. His column C data is KSU. So let's say the next week, week 2, jrjohnson1500 enters column C data as MICH instead of KSU. The program would use his column C data from the latest week, which is week 2. So the Rankings sheet would process jrjohnson1500 in column B and MICH in column C.

    Now, let's say jrjohnson1500 does not submit any entries for the next 4 weeks. The Rankings would still process his data with his name and his week 2 column C data which was MICH because that is his latest data.

    Probably not the best example but does that make sense?

  16. #16
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591
    Hi

    Another go.


    Please Login or Register  to view this content.
    We'll get there yet....


    rylo

  17. #17
    Registered User
    Join Date
    08-06-2006
    Posts
    36
    That looks like it did the trick!

    One quick question...I know you indicated that the names on the Rankings must process a certain letter case, upper, lower or proper. Does this mean there is no command for it to take the case either/or as opposed to all one case the same as it does now? For example, if there is the name jrjohnson1500 and another name Maxx40, there is no way to have it process as such without changing the case of either?

  18. #18
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591
    Hi

    OK, try this.

    Please Login or Register  to view this content.
    It will take the first appearance of the name, and use that casing throughout.

    rylo

  19. #19
    Registered User
    Join Date
    08-06-2006
    Posts
    36
    Wow, this is perfect! You have been a really big help to me. Thank you so much rylo!

  20. #20
    Registered User
    Join Date
    08-06-2006
    Posts
    36

    Re: Matching/Adding

    My apologies for dumpster diving this thread but I have a small request from rylo, if he's still around, or anyone else. rylo provided me with this macro a couple years ago and my spread sheet changed very slightly. I attempted to make the minor change without success.

    My change was simply adding a few rows to the very top of the "Weeks" sheets which disrupted the macro calculation on the "Rankings" sheet. How can I change the code to calculate the "Weeks" sheets starting in row 7 instead of row 4, as before?

    I'll attach what the new sheet looks like. You can see rows 1, 2 and 3 were simply inserted into the top of the Week(s) sheet and that messed up the macro calculation. The macro was set up so the "Rankings" sheet will the take data from columns B, C, D, E and F in the subsequent "Weeks" sheets. To see how the macro currently works, delete the first three rows of the Week 1 sheet and run the macro..results will show up in the Rankings sheet.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by Bullfn33; 07-30-2009 at 01:48 PM.

  21. #21
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Matching/Adding

    Hi

    Try changing the row
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    rylo

  22. #22
    Registered User
    Join Date
    08-06-2006
    Posts
    36

    Re: Matching/Adding

    Man, I don't know how I missed that row. I saw
    HTML Code: 
    near the top and tried to change that B4 to B7 but must have overlooked the right row with B4.

    Yes, your suggestion worked. thanks again rylo!

  23. #23
    Registered User
    Join Date
    08-06-2006
    Posts
    36

    Re: Matching/Adding

    This post is for rylo or anyone else who might be able to implement some small changes to this macro that rylo made several years ago. If someone could help I could send a small PayPal donation if you PM me your email.

    I have attached a TEST workbook that contains a full working example of the same macro that is posted in this thread. Another attachment is the new 'rankings' sheet that I would like to have applied to the TEST workbook in place of the old 'rankings' sheet. The new rankings sheet has a few more columns and the order of the original columns is changed slightly. I have attached an image to illustrate the new columns added and the one column which needs to be moved.

    The macro for the new rankings sheet just needs to be able to do the same thing with the new and moved columns. That is, calculate the data from all subsequent sheets, then rank and sort the users based on column L. The attachments should be pretty self explanatory and you can run the macro in the TEST workbook to see how it works.

    thanks in advance to anyone who is able to help!
    Attached Images Attached Images
    Attached Files Attached Files

  24. #24
    Registered User
    Join Date
    08-06-2006
    Posts
    36

    Re: Matching/Adding

    anyone able to help with this?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1