+ Reply to Thread
Results 1 to 5 of 5

Is there any solution to arrange data accordingly to ID? example included.

  1. #1
    Registered User
    Join Date
    09-07-2017
    Location
    Malaysia
    MS-Off Ver
    2013
    Posts
    11

    Question Is there any solution to arrange data accordingly to ID? example included.

    Dear all.,
    Im new in using excel, and Im not a computer expertise.
    Im sorry if I'm not using the right term in excel.
    Here is my problem, I was given a bulk of data which consist about 20K information.
    I need to arrange the data accordingly from 2 different Excel worksheets; Excel A and Excel B.
    What i need to do is i have to transfer the FUNCTION from Excel B to FUNCTION in excel A.
    The ID from Excel A may sometimes absent in Excel B, same goes to Excel B.
    Moreover the ID can be redundant in Excel A.

    Here is an example;

    Excel A
    A B
    1 ID FUNCTION
    2 S1
    3 S2
    4 S4
    5 S4
    6 S6
    7 S7
    8 S9
    9 S9
    10 S12
    11 S15

    Excel B
    A B C
    1 ID NAME FUNCTION
    2 S1 ALAN SLEEP
    3 S2 NATHAN EAT
    4 S3 ANNA READ
    5 S4 DANIEL SWIM
    6 S10 ROBERT PULL
    7 S11 SAMUEL PUSH
    8 S12 SID SPEAK
    9 S13 ALEX SHOUT
    10 S14 HARRY RUN
    11 S15 TIM WALK

    What I need to do is, I have to arrange the data accordingly, as a result of this in Excel A:

    Excel A
    A B
    1 ID FUNCTION
    2 S1 SLEEP
    3 S2 EAT
    4 S4 SWIM
    5 S4
    6 S6 NA
    7 S7 NA
    8 S9 NA
    9 S9
    10 S12 SPEAK
    11 S15 WALK

    As you can see, the ID in Excel A that have no pairs with any FUNCTION in Excel 2 is written as NA.
    Any redundancy of data, only the first will have the FUNCTION, and the following will be blank space.
    The NAME in Excel B is not required in Excel A.

    Can anyone help me to sort this problem?
    It really needs to take me weeks to solve this by copy and paste for thousands of data.
    Your help is very appreciated.
    Thank you.
    Last edited by azrul94; 09-07-2017 at 10:18 PM. Reason: Error in arranged the data in table

  2. #2
    Forum Expert
    Join Date
    08-02-2013
    Location
    Québec
    MS-Off Ver
    Excel 2003, 2007, 2013
    Posts
    1,412

    Re: Is there any solution to arrange data accordingly to ID? example included.

    Hi,

    Assuming a header in row1 and that your data (first ID) starts in row 2, try the following formula

    Please Login or Register  to view this content.
    See attached file
    Attached Files Attached Files
    GC Excel

    If this post helps, then click the star icon (*) in the bottom left-hand corner of my post to Add reputation.

  3. #3
    Registered User
    Join Date
    09-07-2017
    Location
    Malaysia
    MS-Off Ver
    2013
    Posts
    11

    Re: Is there any solution to arrange data accordingly to ID? example included.

    Thank you, i try it out.

  4. #4
    Registered User
    Join Date
    09-07-2017
    Location
    Malaysia
    MS-Off Ver
    2013
    Posts
    11

    Re: Is there any solution to arrange data accordingly to ID? example included.

    Quote Originally Posted by GC Excel View Post
    Hi,

    Assuming a header in row1 and that your data (first ID) starts in row 2, try the following formula

    Please Login or Register  to view this content.
    See attached file
    May i know what b!$A$2:$C$11,3,0 stands for?

  5. #5
    Registered User
    Join Date
    09-07-2017
    Location
    Malaysia
    MS-Off Ver
    2013
    Posts
    11

    Re: Is there any solution to arrange data accordingly to ID? example included.

    Anyway this problem has been solved. 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: 0
    Last Post: 10-28-2013, 11:02 AM
  2. Splitting data from cells (examples included)
    By Memort in forum Excel General
    Replies: 8
    Last Post: 04-12-2013, 06:31 AM
  3. Ensuring Data included acroos two Workbooks
    By kiktuo in forum Excel General
    Replies: 3
    Last Post: 04-04-2011, 10:25 AM
  4. how to transfer this data? - formulas included
    By cozandeffect in forum Excel General
    Replies: 1
    Last Post: 12-26-2009, 08:44 PM
  5. data validation-included extra rows
    By bruno1 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-16-2006, 02:00 PM
  6. [SOLVED] Scale: Automatically included latest data
    By Jack Hairston in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 04-27-2006, 11:30 AM
  7. Help! data from pasted table is not included in sum
    By campli in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-12-2006, 05:00 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