+ Reply to Thread
Results 1 to 4 of 4

merging and consolidating two data sets

  1. #1
    Registered User
    Join Date
    10-19-2007
    Posts
    2

    merging and consolidating two data sets

    Hi,
    I have two large data sets that I'm trying to combine (see attached example-Sheet1). The first data set contains a sample name with an associated value and s.d.. The second data set is a subset of the first where some of the samples have other data associated with it multiple times. What I'm trying to do is end up with a worksheet (Sheet2) where I have removed data that is not in both sets and also aligned the data. In addition, I want to add a new column that gives me the number of data points for each sample in the second set. Any ideas?
    Thanks.
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    10-17-2007
    Location
    Washington, DC
    Posts
    103
    This is a one to many relationship, right?

    On the second list (the many), do vlookup to add in the stdev and Value for each of the data points. Sort and delete out any that are #N/A. Then add in a new column =countif(a:a,a1).

    This will give you one list with all of your data points, additional data, stdev, values, and the number of data points for each elements. It does repeat this information for each of the cells, if you want the name, value, stdev, and count to only occur in the first row, then set up a second set of columns with the following formulas:

    if(exact(a2,a1),"",a2) | if(exact(a2,a1),"",b2)...

    This will receate the list and remove the data point if the name is the same one as the record above it. Clearly you would want to do a straight cell reference for your data values that are different on each line.

  3. #3
    Registered User
    Join Date
    10-19-2007
    Posts
    2

    Smile merge data

    Thanks!
    It worked like a charm.

  4. #4
    Registered User
    Join Date
    08-18-2011
    Location
    Naples,Florida
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: merging and consolidating two data sets

    Hi,
    I have one column that has the date in it formatted as mm/dd/yyyy and the next column as the time formatted in 24 hrs clock,, I would like to merge them and be able to see the date and time, I tried =PROPER(B3)&" "&PROPER(C3) but it converts the data into a number instead of the date/time.
    How can I make that happen?
    Alex

+ 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