Results 1 to 6 of 6

Thread: How to copy data from non-depending part of excel table to refreshed monthly table...

  1. #1

    Question How to copy data from non-depending part of excel table to refreshed monthly table...



    Register for a FREE account, and/
    or Log in to avoid these ads!

    How can I copy data from non-depending part of excel table to refreshed monthly table so it keeps the names in the same row as original?

    Hello! I'm a brand new member of this community and I found it the best suited to answer my excel-related question.

    My problem is the following:

    I got a monthly updated table with client names in two (in my case I and J) columns. This, main part of the table, which is prepared by our main office, spreads from column A to AB;
    there is, hovewer, a second part of the table, which is separately prepared. It contains columns from AD to AJ, of which the first two (AD and AE) are of my main concern here, and the rest are about some dates (AF and AG), meeting results (AH and AI, merged) and additional notes (AJ). A person in charge should enter a client's name into column AD and his/her surname into column AE, accordingly.

    Now coming to the point - The names in columns AD and AE should always match the rows where the name we need to enter (only SOME names from the left table are entered) are originally placed in columns I and J. For the reference sake - there are approx +1700 names listed there (in columns I and J) in an average monthly table, and of those, only some 50-100 names we use to work with in right (separately prepared) table.

    So, basically, we enter names (manually or c/p) into right column when we need some particular client - we find where they match a particular row left (from columns I and J) and then we enter them in the SAME row on the right.

    The problem arises, however, every time an updated table comes into power - many more rows are added then, a few are deleted.

    My question is therefore: how to keep all the names already entered into right (non-dependent of the left one) part of the table, so they still match the exact row where they have been entered a previous month, along with all other entered data in neighboring cells? I suppose it should involve playing around with lookup, index and/or match functions so I tried it for some time, but without satisfying results.

    I'd be very grateful if you could help me to get closer to solution. I hope I've been clear enough about the issue; I'll gladly clarify further if needed.
    Thanks.

  2. #2
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    792
    Articles
    0
    Excel Version
    2010
    Its a bit difficult to follow this without an example, but let me see if im anywhere close.
    1 You have a table (A to AB) with Client Names in I and J.
    2 On the same worksheet, you have extended data (AD to AJ) that references some of these clients from AD and AE.
    3 However the extended data is not entered onto the correct rows as far as the data in A to AB is concerned, and
    if you are wanting to rearrange the extended data using the same columns, you will probably need to build a new block,
    (say) AK to AQ to provide space, and then delete AD to AJ when your done.

    Is that anywhere near?

  3. #3
    Quote Originally Posted by Hercules1946 View Post
    Its a bit difficult to follow this without an example, but let me see if im anywhere close.
    1 You have a table (A to AB) with Client Names in I and J.
    2 On the same worksheet, you have extended data (AD to AJ) that references some of these clients from AD and AE.
    3 However the extended data is not entered onto the correct rows as far as the data in A to AB is concerned, and
    if you are wanting to rearrange the extended data using the same columns, you will probably need to build a new block,
    (say) AK to AQ to provide space, and then delete AD to AJ when your done.

    Is that anywhere near?

    First of all, thanks a lot for your reply.
    I'd say that you're "right on targed" regarding ad.1 and ad.2, but I'm not that sure about ad.3.
    I therefore made a greatly simplified table to mimic the real, original one I'm talking about, as jpg file. I hope it will help to clarify my previous post for you.

    Ok, here's the table - let's assume this one is for NOV.2014. As you see there is a pre-prepared, monthly updated part of it, spreading form A to AB. The columns I'm interested about , I and J, named "name" and "surname", are marked yellow for our purpose. The names of the rest of the columns there are omitted, for they are not important in the example. It's sorted by names (col.I), A to Z.

    Now there is another part of the table, spreading form AD-AH, which is prepared for internal office use and contains some data related to clients listed in I and J columns of the left part of the table. Notice that of names listed in I and J columns there are only some used in columns AD and AE. So basically, names we want to work with are picked up from left, taking care to always match the same row in columns AD and AE.

    Ok, so far, so good - names and belonging surnames from left columns are now in their respective rows in right columns.


    Click image for larger version. 

Name:	excel table1.jpg 
Views:	15 
Size:	61.7 KB 
ID:	2807


    Ok now, let's take a look at bottom table; suppose there comes next month (DEC.2014) a new table with client list from the main office. Several new clients are added now, one is (David Rodgers), for the sake of my example, deleted.

    Click image for larger version. 

Name:	excel table2.jpg 
Views:	15 
Size:	94.8 KB 
ID:	2808


    Now comes my "puzzle" - the names (David Rodgers, John Johnson and Peter Ronson) in our right table from NOV.2014 (let's call it "1b") now wouldn't fit their respective rows in table 2 (DEC.2014), if we simply copy them in position AD to AH.
    Of course, it could be done by manual entering, but my guess is - hopefully the correct one - that it's possible to work around this problem using appropriate formula there. Pls keep in mind that the original table consists of +1700 clients in average, and the right part of it sometimes more than 100, so I'm convinced that working around it manually is definitelly not a optimal solution.

    I hope this helped making my first post more clear.
    Last edited by Gulliver; 2014-11-07 at 09:34 PM.

  4. #4
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    792
    Articles
    0
    Excel Version
    2010
    Hello Gulliver
    Yes that has helped a lot. You say "if we copy into AD to AH" so Ive assumed that the data that goes there comes on a separate sheet, and you need a method to integrate this correctly into your table 1.
    For my example, table 2 is on the main sheet (AM to AQ) but the principle is the same for a separate sheet. Im using VLOOKUP to integrate the columns from Table 2 into Cols AD to AH of table 1 as required.
    If this works for you, you will need to adjust the size and location of the formula "table_array" to suit.
    Ive also added an extra column at the start of table 2 to provide a more robust lookup key (Name and Surname combined) as Im thinking that (first) Name alone will cause errors due to duplicates.

    HTH

    Hercules
    Attached Files Attached Files

  5. #5
    Hello Hercules1946; thanks a lot again for your efforts; pls take my appologies for some delay.
    In my example, data from "AD:AH" in table no.1 should be somehow copied into respective place in table no.2, keeping in mind they always refer to their respective rows relative to columns "I:J".
    You remarked: Ive assumed that the data that goes there comes on a separate sheet, and you need a method to integrate this correctly into your table 1. Data on table 1 (in my example) are in fact not physically separated from the rest of the data there, theye're added to table 1 (by us) onto SAME sheet. Therefore I am looking for the way to somehow integrate and/or copy/paste it onto TABLE no.2, as soon as we receive a freshly updated table for the next month (in my example, onto table no.2).
    I "feel" that your chosen method is correct, and that we should use a "VLOOKUP" formula as in your example, however I have to admit that I still struggle with the ways to accomodate your formula into my particular table by trying to adjust it (I c/p'd complete FILLED UP columns AM:AS from table 1 next to AM:AS empty columns in table 2, and then I used, as suggested: =IFERROR(VLOOKUP($I3&$J3,$AM$3:$AS$1800,COLUMNS($AD:AF),FALSE),"" to fit my needs, but I still cannot get a correct data there. Therefore, any further help from you would be much appreciated.
    Last edited by Gulliver; 2014-11-13 at 11:53 AM.

  6. #6
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    792
    Articles
    0
    Excel Version
    2010
    Based on your explanation, I cannot understand what your starting data looks like, and it seems strange that your way to combine is to move a large amount of data onto a sheet with a small amount instead of the other way round. I would need to see an example incuding formulae and pointing out where you are not getting the required results.

Posting Permissions

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