+ Reply to Thread
Results 1 to 9 of 9

Aligning new cells based on existing values

  1. #1
    Registered User
    Join Date
    12-05-2017
    Location
    Eastbourne
    MS-Off Ver
    Excel 2016
    Posts
    4

    Aligning new cells based on existing values

    Hi all, I have a quick question in relation to 'alignment'.

    I have a list of 2.5k records of our clients which contains;

    Rank, First Name, Last Name, Address 1, Address 2, Town/City, County, Postcode.

    I then have another list, with the same details but with an 'email' column instead of the 'rank' column. Both spreadsheets are in completely different order.

    I would like to align this 'email' column from this second spreadsheet to my original spreadsheet with the 'rank' in it so I have both. I will need to align this using the 'First Name' AND 'Last Name' fields, as there will be multiple Joe's in the list and hence need alignment based on both - first name and last name. From what I understand what I need to do is create an empty column with a formula to join up first name and last name so that I can find duplicates using both fields in one;

    Joe | Smith | JoeSmith

    After doing this in both spreadsheets, I would like to tell excel to find 'JoeSmith' from spreadsheet 2 and align it along with its email address next to 'JoeSmith' in spreadsheet 1. I'm looking to achieve the below;

    Joe Smith | Rank | Email | Address 1 | address 2 | Town/City | County | postcode.

    I'd like to do this across all 2.5k records.

    Is there a simple way of doing this?

    Any feedback will help massively!
    Thank you so much in advance!

  2. #2
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: Aligning new cells based on existing values

    Use Merge in Get&Transform (PowerQuery) or attach representative desensitive example excel file

    To attach an Excel file to your post,
    • click Go Advanced,
    • scroll down until you see Manage Attachments,
    • click that and select Browse,
    • select your file and click Open,
    • click Upload and you will see your attachment below Upload Files from a website
    • click Close this window,
    • click Submit reply

    After that you should see attachment in your post

  3. #3
    Registered User
    Join Date
    12-05-2017
    Location
    Eastbourne
    MS-Off Ver
    Excel 2016
    Posts
    4

    Re: Aligning new cells based on existing values

    I am unable to include the original document due to confidentiality issues although please see attached a smaller example.

    You will find 3 worksheets - one sheet with the rank, one sheet with the email. Please note these are in a completely different order.
    &Finally you will find a sheet with an example of what I am aiming to achieve - you will see that despite the differences in the order between the two sheets, the emails are aligned to the relevant person.

    I'd like to do this for the whole list of 2.5k people based on their names only.

    Thank you for your reply!
    Attached Files Attached Files

  4. #4
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Aligning new cells based on existing values

    This is csv not excel file

  5. #5
    Registered User
    Join Date
    12-05-2017
    Location
    Eastbourne
    MS-Off Ver
    Excel 2016
    Posts
    4

    Re: Aligning new cells based on existing values

    Sorry - please see attached.
    Attached Files Attached Files

  6. #6
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: Aligning new cells based on existing values

    is that what you want?
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    12-05-2017
    Location
    Eastbourne
    MS-Off Ver
    Excel 2016
    Posts
    4

    Re: Aligning new cells based on existing values

    Yup - that's exactly what I'm after.
    The issue is I need to so this for 2,500 records and hence align 2,500 emails to the correct person.

  8. #8
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Aligning new cells based on existing values

    What's the problem?
    Do the same for whole original tables and you will get correct result

  9. #9
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Aligning new cells based on existing values

    If that takes care of your original question, & to say Thanks, please click on Add Reputtion (bottom left corner of the post of the person(s) who helped you)
    then select Thread Tools from the menu (top right corner of your thread) and mark this thread as SOLVED.
    If you did it already - ignore it.
    Thank you.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 1
    Last Post: 08-26-2014, 04:09 AM
  2. aligning cells based on matching data in column pairs
    By freshfor88 in forum Excel General
    Replies: 2
    Last Post: 08-13-2014, 01:32 PM
  3. Replies: 6
    Last Post: 04-11-2014, 04:45 AM
  4. Replies: 14
    Last Post: 08-01-2013, 06:34 AM
  5. [SOLVED] Graciously requesting assistance aligning columns based on identical data in cells
    By roking in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-08-2012, 01:34 PM
  6. Changing values based on existing data
    By Eddie Lives On in forum Excel Programming / VBA / Macros
    Replies: 19
    Last Post: 01-24-2011, 10:56 AM
  7. Replies: 1
    Last Post: 07-01-2006, 05:40 PM

Tags for this Thread

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