+ Reply to Thread
Results 1 to 32 of 32

Updating 2 tables by shifting down rows based upon the other table

  1. #1
    Registered User
    Join Date
    06-07-2013
    Location
    Here
    MS-Off Ver
    Excel 2010
    Posts
    18

    Updating 2 tables by shifting down rows based upon the other table

    Hello,

    I'm new to this forum and it's the first time I can't find a solution to my problem myself (of course I have checked many forum topics but to no avail).

    The situation:

    I have 2 tables on 1 sheet and want to combine them into 1 or update, as you will.
    The table details can be seen in the sample file I attached.
    What I would like to do now is compare both tables in that sheet, based on multiple criteria (combination of emp ID, date and a number) and if that combination does not exist in table 1 but does in table 2, a blank row should be added on the same rownumber that contains the data in table 2 (so moving the rest of the cells in table 1 down) and vice versa.

    Please view the sample file I attached.
    (this is a fictive example, there are more rows and columns in the actual file)

    As you can see, the number column is based on the date, if the date only shows up once per emp id, it will give a 1, if it shows up twice, it will show "1" for the first occurrence, "2" for the 2nd occurrence etc etc...
    (Original)
    Now based on the combination of the date, emp id and number column, I'd like to add blank rows in either table.
    (Updated)
    As you can see, every "required" row has been shifted down so that the corresponding lines would be on the same row number.
    I have no idea how I can do this but I'm guessing this will require vba as you probably can't shift down using a formula.

    Thanks in advance.

    ps: I have also crossposted, which can be found here: http://www.mrexcel.com/forum/excel-q...lete-list.html
    Attached Files Attached Files
    Last edited by chall04; 06-11-2013 at 05:09 AM.

  2. #2
    Registered User
    Join Date
    01-23-2010
    Location
    Moscow
    MS-Off Ver
    Excel 2007
    Posts
    27

    Re: Updating 2 tables by shifting down rows based upon the other table

    I have written some code. The code combines 2 tables into 1.

    sample(2).xlsm

  3. #3
    Registered User
    Join Date
    06-07-2013
    Location
    Here
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: Updating 2 tables by shifting down rows based upon the other table

    Thank you for the code IgorSharov.
    That is sort of what I need.
    The name, date, emp id can be combined into 1 column each but the data should be in their respective columns.
    i.e. if a row shows up in the 2nd table which is not in the first, the name, date and emp id should be added to the combined column, if the row in the 2nd table and in the first, there is no need to add the name, date, emp id again and vice versa.

    But what I would prefer is (as show in the sample file) to "just" have every non existing combination in either table, to add a blank row, so that if the combination is the same, they are on the same row.

    But thanks for your help so far!

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,051

    Re: Updating 2 tables by shifting down rows based upon the other table

    Try the attached
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    06-07-2013
    Location
    Here
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: Updating 2 tables by shifting down rows based upon the other table

    Yes that's brilliant jindon.
    I've tested it and it seems to do what I want.
    Though it seems to have an issue with the date when copying from sheet 1 to sheet 2.
    It converts the first 5-6 dates it seems.
    It takes the days as month and the month as day.
    Any idea on how I can fix this?

    Thanks a bunch!

  6. #6
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,051

    Re: Updating 2 tables by shifting down rows based upon the other table

    Isn't it just a cell formatting?

  7. #7
    Registered User
    Join Date
    06-07-2013
    Location
    Here
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: Updating 2 tables by shifting down rows based upon the other table

    I believe it is, but it seems to swap the date and month eg: 10/04 = 10th of april, it swaps it to 04/10 = 4th of october.
    The cellformat is set as "modified", not sure what the English term is, but change the cellformat doesn't seem to help.

  8. #8
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,051

    Re: Updating 2 tables by shifting down rows based upon the other table

    What happen if you change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    06-07-2013
    Location
    Here
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: Updating 2 tables by shifting down rows based upon the other table

    Quote Originally Posted by jindon View Post
    What happen if you change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    Works like a charm.
    Brilliant!

    EDIT: little question, how can I modify the code so I can change the fields you base the sorting on?
    And for some reason, in the actual file, it copies the date of the 2nd table as a number and not as a date although the cellformat is date.
    Last edited by chall04; 06-10-2013 at 09:43 AM.

  10. #10
    Registered User
    Join Date
    06-07-2013
    Location
    Here
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: Updating 2 tables by shifting down rows based upon the other table

    The date issue seems to be solved (for now).
    In the real file, it puts a line after almost every entry for each table.
    I think it's got something to do with the fields it's using to determine if rows are equal.

  11. #11
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,051

    Re: Updating 2 tables by shifting down rows based upon the other table

    Did you format the date column in Blad2 as date ?

  12. #12
    Registered User
    Join Date
    06-07-2013
    Location
    Here
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: Updating 2 tables by shifting down rows based upon the other table

    Quote Originally Posted by jindon View Post
    Did you format the date column in Blad2 as date ?
    Yes I did. It works without a problem in the sample file but in the real file (with a few more columns) it seems not to function properly. (date shows as date, the rows don't setup correct, it's like it finds a difference in each tablerow)

  13. #13
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,051

    Re: Updating 2 tables by shifting down rows based upon the other table

    Quote Originally Posted by chall04 View Post
    but in the real file (with a few more columns)
    Then I need to see the data.

    The code posted was specially designed to the file you posted.

  14. #14
    Registered User
    Join Date
    06-07-2013
    Location
    Here
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: Updating 2 tables by shifting down rows based upon the other table

    Ok, my bad.
    This file has the exact amount of columns I'm using.
    The ID and Date column being the important ones to sort on (maybe with the number column, don't know how you did it).
    What I will do before your magic happens, is sorting the table based on the ID, don't know if that's important to know.
    Attached Files Attached Files

  15. #15
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,051

    Re: Updating 2 tables by shifting down rows based upon the other table

    Change to
    Please Login or Register  to view this content.

  16. #16
    Registered User
    Join Date
    06-07-2013
    Location
    Here
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: Updating 2 tables by shifting down rows based upon the other table

    Thank you for the reply but the changes are the same as I made but to no avail.
    I'm still having the same issue.
    Just checking to be sure but it doesn't matter if your code is placed between other code, right?
    Nor does it matter that my actual file consists of about 15 sheets (I changed the name of the sheets in the code accordingly).
    It also seems to "re-sort" the tables on the 2nd sheet by name instead of ID.

  17. #17
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,051

    Re: Updating 2 tables by shifting down rows based upon the other table

    Slow access here....

    See attached
    Attached Files Attached Files

  18. #18
    Registered User
    Join Date
    06-07-2013
    Location
    Here
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: Updating 2 tables by shifting down rows based upon the other table

    Jindon, thank you for the attached file, however, the problem remains the same.
    The attachment shows you what the code mostly does in my real file (your code works perfectly in your provided sample file).
    I do have to say that if the name in table 1 and table 2 are different (not case sensitive but actually written differently), the code seems to group the data about 1 person in the 2nd table together and then in the first table.
    eg: lets say that person Xa has 8 rows of data in table 1 and person Xb 8 rows of data in table 2 (both X are the same person, might be written a bit differently)
    it will group all rows together of Xb and after those 8 rows, it will show the 8 rows of Xa:
    -Xb
    -Xb
    -Xb
    -Xb
    -Xb
    -Xb
    -Xb
    -Xb
    -Xa
    -Xa
    -Xa
    -Xa
    -Xa
    -Xa
    -Xa
    -Xa
    Attached Files Attached Files
    Last edited by chall04; 06-12-2013 at 02:28 AM.

  19. #19
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,051

    Re: Updating 2 tables by shifting down rows based upon the other table

    I don't understand what you want this to do.

    Hope someone else can help you.

  20. #20
    Registered User
    Join Date
    06-07-2013
    Location
    Here
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: Updating 2 tables by shifting down rows based upon the other table

    Quote Originally Posted by jindon View Post
    I don't understand what you want this to do.

    Hope someone else can help you.
    Well, it should do actually as it does in the sample you provided (works perfectly there) but seems to malfunction in my real file and I have no clue why.
    If you could tell me what the code exactly does or what it's based on to check if rows are similar...

  21. #21
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,051

    Re: Updating 2 tables by shifting down rows based upon the other table

    If I don't see what you called "Real file", I can not say anything.

    My code groups when the names are the same.

  22. #22
    Registered User
    Join Date
    06-07-2013
    Location
    Here
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: Updating 2 tables by shifting down rows based upon the other table

    Ah I see, but the names will always be different if it's case sensitive.
    Could you tell me what I have to modify in the code to group by ID?

    Thanks

  23. #23
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,051

    Re: Updating 2 tables by shifting down rows based upon the other table

    Actually I used first 4 columns as a Unique key.

    Name, Date, Week, ID

    If you want just ID as a unique key then change
    Please Login or Register  to view this content.
    To
    Please Login or Register  to view this content.

  24. #24
    Registered User
    Join Date
    06-07-2013
    Location
    Here
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: Updating 2 tables by shifting down rows based upon the other table

    Ah yes, that does seem to work better.
    And what if I wanted to base it on 2 columns which were not located next to eachother?

    Do I change something here then?

    Please Login or Register  to view this content.
    And if so, what do I change then?

  25. #25
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,051

    Re: Updating 2 tables by shifting down rows based upon the other table

    No loop

    Just change it like
    Please Login or Register  to view this content.
    for 1st and 3rd column.

  26. #26
    Registered User
    Join Date
    06-07-2013
    Location
    Here
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: Updating 2 tables by shifting down rows based upon the other table

    You sir are a genius.
    It seems to work like a charm at first glance.
    If only I knew what the rest of your code does :p.

    Anyway, I will doublecheck just to be sure and if it's all good, I will mark this topic as solved ^^. (might be tomorrow)

    Thanks for all your help!

    ps: Yes, an explanation of the code would be sweet .

  27. #27
    Registered User
    Join Date
    06-07-2013
    Location
    Here
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: Updating 2 tables by shifting down rows based upon the other table

    Running all my stuff on a new set of files gives me the following:

    Error 13
    Type mismatch
    on this line:
    txt = a(i, 4) & Chr(2) & a(i, 2)

    It works on the old files though.
    Probably a simple fix?
    The cellformat is the same as my old files.

  28. #28
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,051

    Re: Updating 2 tables by shifting down rows based upon the other table

    Need to see the file that give such error.

  29. #29
    Registered User
    Join Date
    06-07-2013
    Location
    Here
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: Updating 2 tables by shifting down rows based upon the other table

    *Double post, sorry*
    Last edited by chall04; 06-14-2013 at 02:21 AM.

  30. #30
    Registered User
    Join Date
    06-07-2013
    Location
    Here
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: Updating 2 tables by shifting down rows based upon the other table

    Yes I do think that would make it easier but the file is quite "sensitive" so I cannot do that.
    What the file is made of:
    It contains about 15 sheets
    sheet 13 contains data, put together from the first 10 sheets
    sheet 14 contains data imported from another file
    sheet 15 (the sheet I'm running your code on) consists of a table with the data from sheet 13 (so I fill it in like: =sheet13!A2 etc etc...) and a table from sheet 14 (=sheet14!a2)
    The only thing that changes is the data in the sheets. The structure of the sheets remain the same.
    Whenever I run your code with the old data (month april), it works without problems.
    If I try to run your code with new data (month may), it gives me that error.
    The line I previously posted uses the 4th column (ID, which is a number of 11 digits and has a general cellformat) and then the 2nd column (date, which has a date format of d/mm/yyyy) to sort/compare the tables.
    I don't know if this is of any use to you, but I think this is the best I can give you.

    EDIT: I was trying to post the code of the entire macro I'm using on that last sheet but it's too many chars to post here.
    Can I send it to you via mail or PM?

    EDIT2: For some reason when my code gets the info from the other sheets, the date in my first table gets a date cellformat, but the date in my 2nd table gets a general cellformat.
    Don't know if that could be causing the problem. Changing the cellformat doesn't seem to help.
    Last edited by chall04; 06-14-2013 at 03:02 AM.

  31. #31
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,051

    Re: Updating 2 tables by shifting down rows based upon the other table

    You can still create a dummy data set with exact same format.

    All I need to see is CORRECT data layouts and the EXACT result that you want.

    If you can not do it, you must forget about this.

  32. #32
    Registered User
    Join Date
    06-07-2013
    Location
    Here
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: Updating 2 tables by shifting down rows based upon the other table

    I figured it out myself, it was getting data from somewhere else and the fields I was comparing had a value of #NA, after changing that, the problem was fixed.

    Thanks for all your help.

+ 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