+ Reply to Thread
Results 1 to 12 of 12

Combine Data and Sort

  1. 05-02-2013, 01:13 PM #1
    ksnyder
    ksnyder is offline
    Registered User
    Join Date
    05-02-2013
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    8

    Combine Data and Sort

    I have two images that I hope will help explain what I would like to accomplish. Please let me know if more information is needed. Any help would be greatly appreciated.

    spreadsheet_01.PNG

    Column A is static data that is preset in the spreadsheet. Column B and C data will be collected from students through the use of a Google Form, so the student number will be randomly placed in the column and not all student's may fill out the form. Column C represents a class section that they make. I am currently using the formula:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    to combine the values in column B and C. However, I need the student number values in column D to line up with the student number values in column A. For example, student choice "1064 Basket Weaving" in column D is in row 11 to match up with the static data in column A.

    I may be making this harder than needed, but to accomplish my task I thought about making a new sorted column like the image below:

    spreadsheet_02.PNG

    In column E, the plan is to use the formula:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    The formula aligns the values with column A if I only have the four digit number in column D, but my hang up is that I need the formula to only look for a match in the first four digits of column D. How can I modify the formula to do that? If there is an easier way to perform this task I am open to ideas. Thank you.
    Reply With QuoteRegister To Reply
  2. 05-02-2013, 02:18 PM #2
    dilipandey
    dilipandey is offline
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Combine Data and Sort

    Hi Ksnyder,

    welcome to the forum.

    Instead of image, upload an excel workbook so that forum members can give a quick practical shot


    Regards,
    DILIPandey
    <click on below * if this helps>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]
    Reply With QuoteRegister To Reply
  3. 05-02-2013, 02:25 PM #3
    ksnyder
    ksnyder is offline
    Registered User
    Join Date
    05-02-2013
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Combine Data and Sort

    Sorry about that. Here is the spreadsheet to give it a go. Thanks.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 05-02-2013, 02:29 PM #4
    Pauleyb
    Pauleyb is offline
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: Combine Data and Sort

    Try this in D2 and drag down
    =IFERROR(A2&" "&VLOOKUP(A2,$B$2:$C$16,2,FALSE),"")
    Adjust the B2:C16 range as appropriate.

    Actually, saw you are using 2003. Slightly different:
    =IF(ISNA(VLOOKUP(A2,$B$2:$C$16,2,FALSE)),"",A2&" "&VLOOKUP(A2,$B$2:$C$16,2,FALSE))
    Pauley
    --------
    If I helped with your issue, I'd appreciate a rep bump (hit the '*' icon to the bottom left of this post).
    Reply With QuoteRegister To Reply
  5. 05-02-2013, 02:34 PM #5
    dilipandey
    dilipandey is offline
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Combine Data and Sort

    Okay. . use below formula:-

    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    see attachment:- Request Online Test.xlsx


    Regards,
    DILIPandey
    <click on below * if this helps>
    Reply With QuoteRegister To Reply
  6. 05-02-2013, 02:41 PM #6
    ksnyder
    ksnyder is offline
    Registered User
    Join Date
    05-02-2013
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Combine Data and Sort

    That works! Thank you!
    Reply With QuoteRegister To Reply
  7. 05-02-2013, 02:47 PM #7
    dilipandey
    dilipandey is offline
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Combine Data and Sort

    Great..!!

    not sure if you are saying for Pauleys or myself.. but no worries.. cheers

    Please mark this thread as [SOLVED]. thanks.

    Regards,
    DILIPandey
    <click on below * if this helps>
    Reply With QuoteRegister To Reply
  8. 05-02-2013, 02:52 PM #8
    ksnyder
    ksnyder is offline
    Registered User
    Join Date
    05-02-2013
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Combine Data and Sort

    I got Pauleyb's formula to work...thanks for the help. I appreciate it!
    Reply With QuoteRegister To Reply
  9. 05-02-2013, 02:53 PM #9
    dilipandey
    dilipandey is offline
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Combine Data and Sort

    3 cheers to Pauleyb's ...



    Regards,
    DILIPandey
    <click on below * if this helps>
    Reply With QuoteRegister To Reply
  10. 05-02-2013, 03:10 PM #10
    ksnyder
    ksnyder is offline
    Registered User
    Join Date
    05-02-2013
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Combine Data and Sort

    Request Online Test Studnet Number End.xlsxOne small change. How would I use the formula if the student number was at the end instead of the beginning? I need to be able to group the students by their selection. Thank you.
    Reply With QuoteRegister To Reply
  11. 05-02-2013, 03:27 PM #11
    Pauleyb
    Pauleyb is offline
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: Combine Data and Sort

    If you are grouping the studends by selection, then I would just highlight your the data in columns B and C, and then sort on C.

    You could use a formula similar to what I provided and 'swap' the A2 and the VLOOKUP(..) to put the class first in the text string, but it seems redundant since you'll just end up sorting that list, too.
    Reply With QuoteRegister To Reply
  12. 05-02-2013, 03:34 PM #12
    ksnyder
    ksnyder is offline
    Registered User
    Join Date
    05-02-2013
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Combine Data and Sort

    Yeah, I found that I can get the results I was looking for with the filter options. Thanks for all the help. I appreciate it.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1