Combine data from multiple rows onto one row in separate columns

My table looks like this:

Sample_#  data1  data2  data3  data4 etc..
0000001     0.1                   0.2
0000001                  2.5
0000001                                           0.5
0000002     0.2                  0.2
0000002                   3.1
0000003     0.1                  0.2

I receive data at different points in time and it thus I end up with 
multiple sample_#'s and various data columns filled in. I would like to 
combine all the data for each sample into one row.

I would like it to look like this:

Sample_#  data1  data2  data3  data4 etc..
0000001    0.1       2.5       0.2       0.5
0000002    0.2       3.1       0.2
0000003    0.1                    0.2

Any suggestions for automating amalgamating this data into the format I 
would like?

Please help. 
Thank you.
0
Utf
1/6/2010 2:18:01 AM
access 16762 articles. 3 followers. Follow

5 Replies
1328 Views

Similar Articles

[PageSpeed] 17

On Tue, 5 Jan 2010 18:18:01 -0800, Balbina <Balbina@discussions.microsoft.com>
wrote:

>My table looks like this:
>
>Sample_#  data1  data2  data3  data4 etc..
>0000001     0.1                   0.2
>0000001                  2.5
>0000001                                           0.5
>0000002     0.2                  0.2
>0000002                   3.1
>0000003     0.1                  0.2
>
>I receive data at different points in time and it thus I end up with 
>multiple sample_#'s and various data columns filled in. I would like to 
>combine all the data for each sample into one row.
>
>I would like it to look like this:
>
>Sample_#  data1  data2  data3  data4 etc..
>0000001    0.1       2.5       0.2       0.5
>0000002    0.2       3.1       0.2
>0000003    0.1                    0.2
>
>Any suggestions for automating amalgamating this data into the format I 
>would like?
>
>Please help. 
>Thank you.

Your data input process is clearly at fault here: it shouldn't be adding
multiple records per sample, it should be updating existing sample records if
they are there and only adding a new one if there isn't!

Will you ever have two different numbers for a given field for a given sample,
e.g.

Sample_#  data1  data2  data3  data4 etc..
0000001     0.1                   0.2
0000001                  2.5
0000001                                           0.5
0000001     0.5

If so, what do you want to happen? Store 0.1, 0.5, 0.6? Generate an error
message?

If you will NEVER have this situation - each field will have only one non-null
value for all instances - you could create a second identically structured
table and fill it using an Append query:

INSERT INTO newtable
SELECT table.[Sample_#], Max([data1]) AS Data1, Max([data2]) AS Data2,
Max([data3]) AS data3, Max([data4]) AS Data4
FROM table
GROUP BY table.[Sample_#];

-- 

             John W. Vinson [MVP]
0
John
1/6/2010 6:39:44 AM

"John W. Vinson" wrote:

> On Tue, 5 Jan 2010 18:18:01 -0800, Balbina <Balbina@discussions.microsoft.com>
> wrote:
> 
> >My table looks like this:
> >
> >Sample_#  data1  data2  data3  data4 etc..
> >0000001     0.1                   0.2
> >0000001                  2.5
> >0000001                                           0.5
> >0000002     0.2                  0.2
> >0000002                   3.1
> >0000003     0.1                  0.2
> >
> >I receive data at different points in time and it thus I end up with 
> >multiple sample_#'s and various data columns filled in. I would like to 
> >combine all the data for each sample into one row.
> >
> >I would like it to look like this:
> >
> >Sample_#  data1  data2  data3  data4 etc..
> >0000001    0.1       2.5       0.2       0.5
> >0000002    0.2       3.1       0.2
> >0000003    0.1                    0.2
> >
> >Any suggestions for automating amalgamating this data into the format I 
> >would like?
> >
> >Please help. 
> >Thank you.
> 
> Your data input process is clearly at fault here: it shouldn't be adding
> multiple records per sample, it should be updating existing sample records if
> they are there and only adding a new one if there isn't!
> 
> Will you ever have two different numbers for a given field for a given sample,
> e.g.
> 
> Sample_#  data1  data2  data3  data4 etc..
> 0000001     0.1                   0.2
> 0000001                  2.5
> 0000001                                           0.5
> 0000001     0.5
> 
> If so, what do you want to happen? Store 0.1, 0.5, 0.6? Generate an error
> message?
> 
> If you will NEVER have this situation - each field will have only one non-null
> value for all instances - you could create a second identically structured
> table and fill it using an Append query:
> 
> INSERT INTO newtable
> SELECT table.[Sample_#], Max([data1]) AS Data1, Max([data2]) AS Data2,
> Max([data3]) AS data3, Max([data4]) AS Data4
> FROM table
> GROUP BY table.[Sample_#];
> 
> -- 
> 
>              John W. Vinson [MVP]
> .

Thank you. That works really well. It might happen that I would have more 
than one value for a given field, but that would be an error. Any suggestions 
for trapping that error?
0
Utf
1/6/2010 5:42:02 PM
Try this --
INSERT INTO newtable
SELECT table.[Sample_#], IIF(Max([data1]) = Min([data1]), Max([data1]), 
"Error")AS Data1, IIF(Max([data2]) = Min([data2]), Max([data2]), "Error") AS 
Data2, IIF(Max([data3]) = Min([data3]), Max([data3]), "Error") AS Data3, 
IIF(Max([data4]) = Min([data4]), Max([data4]), "Error")AS Data4 
FROM table
GROUP BY table.[Sample_#];

-- 
Build a little, test a little.


"Balbina" wrote:

> 
> 
> "John W. Vinson" wrote:
> 
> > On Tue, 5 Jan 2010 18:18:01 -0800, Balbina <Balbina@discussions.microsoft.com>
> > wrote:
> > 
> > >My table looks like this:
> > >
> > >Sample_#  data1  data2  data3  data4 etc..
> > >0000001     0.1                   0.2
> > >0000001                  2.5
> > >0000001                                           0.5
> > >0000002     0.2                  0.2
> > >0000002                   3.1
> > >0000003     0.1                  0.2
> > >
> > >I receive data at different points in time and it thus I end up with 
> > >multiple sample_#'s and various data columns filled in. I would like to 
> > >combine all the data for each sample into one row.
> > >
> > >I would like it to look like this:
> > >
> > >Sample_#  data1  data2  data3  data4 etc..
> > >0000001    0.1       2.5       0.2       0.5
> > >0000002    0.2       3.1       0.2
> > >0000003    0.1                    0.2
> > >
> > >Any suggestions for automating amalgamating this data into the format I 
> > >would like?
> > >
> > >Please help. 
> > >Thank you.
> > 
> > Your data input process is clearly at fault here: it shouldn't be adding
> > multiple records per sample, it should be updating existing sample records if
> > they are there and only adding a new one if there isn't!
> > 
> > Will you ever have two different numbers for a given field for a given sample,
> > e.g.
> > 
> > Sample_#  data1  data2  data3  data4 etc..
> > 0000001     0.1                   0.2
> > 0000001                  2.5
> > 0000001                                           0.5
> > 0000001     0.5
> > 
> > If so, what do you want to happen? Store 0.1, 0.5, 0.6? Generate an error
> > message?
> > 
> > If you will NEVER have this situation - each field will have only one non-null
> > value for all instances - you could create a second identically structured
> > table and fill it using an Append query:
> > 
> > INSERT INTO newtable
> > SELECT table.[Sample_#], Max([data1]) AS Data1, Max([data2]) AS Data2,
> > Max([data3]) AS data3, Max([data4]) AS Data4
> > FROM table
> > GROUP BY table.[Sample_#];
> > 
> > -- 
> > 
> >              John W. Vinson [MVP]
> > .
> 
> Thank you. That works really well. It might happen that I would have more 
> than one value for a given field, but that would be an error. Any suggestions 
> for trapping that error?
0
Utf
1/6/2010 10:13:03 PM
Thank you!
0
Utf
1/6/2010 10:34:01 PM
On Wed, 6 Jan 2010 14:13:03 -0800, KARL DEWEY
<KARLDEWEY@discussions.microsoft.com> wrote:

>Try this --
>INSERT INTO newtable
>SELECT table.[Sample_#], IIF(Max([data1]) = Min([data1]), Max([data1]), 
>"Error")AS Data1, IIF(Max([data2]) = Min([data2]), Max([data2]), "Error") AS 
>Data2, IIF(Max([data3]) = Min([data3]), Max([data3]), "Error") AS Data3, 
>IIF(Max([data4]) = Min([data4]), Max([data4]), "Error")AS Data4 
>FROM table
>GROUP BY table.[Sample_#];

Very nice Karl!!! Thanks.
-- 

             John W. Vinson [MVP]
0
John
1/6/2010 11:22:12 PM
Reply:

Similar Artilces:

Get row values into column
I have a row (A:BI) with text or numbers in each cell. However, there are some cells that are blank. B,D,F,H,J,... are the number cells (unless they are blank) and A,C,E,G,I,K,... are the text cells (unless they are blank). I would like to have all the cells that have text in them to be entered into cell A5 and down. Same thing with the number cells except in cell B5 down. Example: A B C D E F 1 PHI 2.3 OTT 3.6 I would like the following: A5 = PHI B5 = 2.3 A6= OTT B6 = 3.6 Anyone know how ...

count cells using multiple criteria
Can anyone show me how to count the number of rows containing two or more criteria? For example, if one column has the text "DO" and another column has the text "RD11" how do I count only those rows that contain both DO and RD11? One way: =SUMPRODUCT(--(A1:A1000="DD"),--(B1:B1000="RD11")) In article <0F41177E-45A0-4B0B-AE33-6BCCF2F3B8D1@microsoft.com>, Alex68 <Alex68@discussions.microsoft.com> wrote: > Can anyone show me how to count the number of rows containing two or more > criteria? For example, if one column has the te...

Data and Other Records
Hi - I'm building a pivot table consisting of counts of a list of items. I want to include only the first 50 items in detail, but lump all the others into an "other" category with a count on it, instead of detail on records above 50. Hopefully this makes some sense! Thanks, Greg. -- groggrog ------------------------------------------------------------------------ groggrog's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27400 View this thread: http://www.excelforum.com/showthread.php?threadid=469155 ...

problem with a macro autofilter to delet rows
Hi, I have this sheet with some if formula applied to it. I want all rows that have false as a value as a result of the if formula to delete. If I try a macro with the False value it doesn=B4t work because it doesn=B4t recognize the value false. If I try doing a macro that searches by that term it can=B4t find it without converting them to values first and I thpught of recording a macro with the autofilter, but what a surprise that when I run it, it doesn=B4t apply the autofilter. What am I doing wrong or how can I delete all those rows with the false value. Thank you so much for ...

Fill last row down for large selection?
A few months ago someone posted a great solution for the following problem which doesn't seem to be working all of a sudden; I have a column with values which has blank rows between values (anywhere from 1 to 10 blank rows) and I need to fill those with the values of the last cell with a value i.e. from AAA BBB CCC DDDD to A few months ago someone posted a great solution for the following problem which doesn't seem to be working all of a sudden; I have a column with values which has blank rows between values (anywhere from 1 to 10 blank rows) and...

Making data span 2 columns
Hi, I'm new to this community and new to the RMS system. I am trying to modify the PrintTransactionDetails sub in the receipt.xml to be 2 columns and put the entry.description information onto another row. The person who writes the descriptions likes to be wordy and I need the entire 40 columns in order to hold the text. I changed the 3 columns into 2 with a format of 71% for the item number or description and 29% for the sales amount. The description and a null field occupy the first row and the item number and amount occupies the second row. I have the transaction d...

Macro to delete an entire row with cells missing a specific chartacter #2
Hey Harald, This is the script I am using to remove "IS" Sub Findanddelete_IS() Dim rng As Range Dim what As String what = "IS" Do Set rng = ActiveSheet.UsedRange.Find(what) If rng Is Nothing Then Exit Do Else Rows(rng.Row).Delete End If Loop End Sub So...my programming experience is a tiny bit limited, not sure how t delete anything missing the IS as opposed to containing the IS. understand in theory but my knowledge of syntax is the limiter. Harald Staff Wrote: > Hi Asf > > This is far more useful if you figure it out yourself, since you'r > pr...

multiplying an enitre column.
In excel I have four columns all containing 500 cells. These cells represent wholsale pricing. I want to muliply each column by 2 so they represent retail pricing. Robert Back u or save your data FIRST! then in an empty cell outside your 4X500 matrix enter the number 2. Copy this same cell (containging the Value 2). Highlight the full range A1:D500, then click on the Menu - Edit, Paste-Special, Click the Multiply button, then OK erase the cell containing the 2. HTH "Robert" wrote: > In excel I have four columns all containing 500 cells. These cells represent > who...

How can i change chart data point format based on value?
I have a simple column chart with a single data series. I would like to change the color of an individual data point column depending on value. Thanks very much. You want to make a conditional chart: http://peltiertech.com/Excel/Charts/ConditionalChart1.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "Harold96" <Harold96@discussions.microsoft.com> wrote in message news:3674450D-70A9-4227-B29A-36262B7BE723@microsoft.com... >I have a simple column chart with a single data series. I would...

bar graph column label alignment
I'm using a stacked bar graph and would like to align the x axis label with the edge of each column instead of having the label centered on each column. See Mike Middleton's Better Histogram: http://www.treeplan.com/better.htm - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "matt" <matt@discussions.microsoft.com> wrote in message news:87811AD0-9334-4408-AF88-167D6FCEC079@microsoft.com... > I'm using a stacked bar graph and would like to align the x axis label...

geographic map data
I would like to find a simple set of geographic map data (Latitude and Longitude) in EXCEL tables for drawing country outline maps for use in EXCEL Charts ...

Calculate total number off cells with data in
Hi All Hope I find you well I have a spreadsheet that has a number of cells that may or may not have data in them depending on curtain criteria. What I would like to be able to do is count the number of cells with data in (data is alphanumerical) and display the total number of cells with data in in a different cell. Any ideas? Hi Gazza, > What I would like to be able to do is count the number of cells with data in (data is alphanumerical) and display the total number of cells with data in in a different cell. Any ideas? > Check out the COUNTA worksheet function. Regards, Jan Kare...

Count all visible rows on a sheet
Hey guys, We need to have a formula that will count all visible rows on a sheet (well in a perticular array actually. What we have is a sheet that looks something like this Name Data1 Data2 Data3 john smith 11 21 33 john smith 12 22 31 john smith 23 333 john smith 14 24 33 john smith 15 25 34 john smith 26 35 john smith 17 3 john smith 18 28 33 john smith 19 29 33 And we have auto filtering enabled on the top row. We would l...

Printing two colors without color separation
I am using Publisher 2007 and have struggled for a year to get adobe to create two color separations. The postscript print driver just doesn't work for me or a colleague that is working on the same file in another city (although I used to be able to print two color separations). Since our printer requires a PDF file I wondered if there was a an easy way to print two separate PDF of the same publisher file by creating a different color scheme or creating unique fonts that print some headers only in one color and rename the same headers with the font colors and lines set to white. I...

Determine if data is trending UP or DOWN
All I posted this question in another area with little activity. Please review my question and reply with any suggestions http://groups.google.com/group/microsoft.public.excel.worksheetfunctinos/browse_thread/thread/51433fe1f0242504/f908dfbcb5ae9487?hl=en#f908dfbcb5ae9487 Ignore all points except the first and the last. If the last point is greater than the first point, the trend is up. -- Gary''s Student - gsnu200719 "Steve" wrote: > All I posted this question in another area with little activity. > Please review my question and reply with any suggestions > ...

Excel 2002: Can I save all files by just one click ?
Hi, I have 10 working files in an active window. There is no Save All button under the file menu. May I know if I can save all the files at one click without having to exit excel ? Thanks Low This delivers it in 2 clicks ... Hold down Shift key, click File > Close All Then click "Yes to All" in the ensuing prompt to save changes Success? Celebrate it, hit the YES below -- Max Singapore --- "Mr. Low" wrote: > I have 10 working files in an active window. > There is no Save All button under the file menu. > May I know if I c...

changing columns in the select names dialog box
Outlook 2003 using Microsoft Exchange Server. 1) Is it possible to change which columns display and in what order in the "select columns" dialog box? 2) I have found that I can remove a column by dragging and sliding a column to the left against the next column on the left. Unfortunately I have removed some columns that I would like to see. How do I get the columns to appear again? Many thanks. Jeff 1) No.=20 2) That's a tricky one! It took a couple of minutes for me to figure = out. Position your mouse pointer exactly over the divider on the left = side of the colu...

Exchange 2000 question regarding one AD object with 2 mailboxes
Help please. Exchange 2000 AD 2003. We have a mailbox for our Support desk. This mailbox is called supportdesk. So the AD object is also called supportdesk. We need to change the name to helpdesk. Prior to doing this we want to advertise the new name to all staff. Is there a way we can make the new AD object called helpdesk and then assign the old supportcenter mailbox to it before deleting the existing supportdesk mailbox . I.e create new account with mailbox called helpdesk and also have mailbox called supportdesk assigned to it as well. So if mail goes to supportdesk mailbox...

Can only populate 1st row of Avery label 8167
I have 13 records in Excel 2007 and tried merging it to word 2007 using avery label 8167. I followed the step-by-step mail merge wizard up to the update labels button. When I select Preview results, I only see the first four records on the first row (should I be seeing 13 records?) I check to find recipients yet all 13 are there. Auto check for errors is ok-no errors found. I can't seem to populate the 2nd, 3rd & 4th rows. Please help... You need to complete the merge. I believe the code for this is "Edit individual labels." -- Suzanne S. Barnhill Microsof...

Copy filtered data (Values only)
The following code copies filtered data: Set Rng = ActiveSheet.AutoFilter.Range Rng.Copy Destination:=Worksheets("Sheet2").Range("A1") but copies values and formulae. (e.g. .PasteSpecial xlPasteValues) How do I modify to the copy only the values Thanks! Set Rng = ActiveSheet.AutoFilter.Range Rng.Copy Worksheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteValues Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "Steve" <Steve@discussions.microsoft.com> wrote in message news:5E54D33F-2...

How to retrieve real data type of function's parameters
Hi all, I have a function which accepts some parameters. When I call this function, I used some casting operation and In this function I want to get the real data type name of these parameters. Can I do it? If I can, how should I do? Please help me. Thanks. If you are looking for something like .NET reflection, you won't find an identical equivalent. But look up "Run-Time Type Information" and "Accessing Run-Time Class Information" on MSDN. -- Regards, Nish [VC++ MVP] http://www.voidnish.com http://blog.voidnish.com "Nguyen Van Binh" <binhnv@...

Time Card Reports - Looking for an Improved One
can someone please share with me an improved Time Card Report? cfo@virtualmagic.net.nospam (please remove the NO spam) Thanks a lot J I forwarded this to John M. He made one that works better -- Elizabeth M. "ParkRanger" <bfgpaintball.com> wrote in message news:eNEc$aomEHA.3608@TK2MSFTNGP09.phx.gbl... > can someone please share with me an improved Time Card Report? > > cfo@virtualmagic.net.nospam (please remove the NO spam) > > Thanks a lot > > J > > Still waiting :( "Elizabeth" <anonymous@discussions.microsoft.com> wrote...

How do you hide .00's in a worksheet & keep the ones that say 10.7
I want to be able to have all my .00's in worksheet as not showing the decimal places, and want to keep all the ones that have decimal places as .75 etc. In other words, I don't want to "hide" the 2 decimal places that actually have cents on there (for example, keep all the 10.75's, 10.68's, but hide all the zeros in 10.00 so that tht zero cents just show as 10 instead of 10.00....)? I don't want to have to go and select all the individual cells that show with zeros and change them to showing zero decimals... Format the cells as General (Format...

data input format
Hi I have a user entry form and need to ensure the correct data format is used when entering. At the moment it is a text box on a VBA data entry form, I want to code the text box to only accept dd/mm/yy format. -- thanks Roy If you get the dat as a string, then you can perform very specific tests on parts of that string. For example: Sub StrictFormat() Dim s As String, i As Integer s = Application.InputBox(prompt:="Date?", Type:=2) If Len(s) <> 8 Then MsgBox "Bad Format" Exit Sub End If ary = Split(s, "/") If UBound...

color coding different data series
Hello! I have about 8 spreadsheets of data that I need to compile into one chart. However, I need to be able to look at the chart and see which spreadsheet a data point is from. Is there a quick way to color code a group of data points from one spreadsheet? I know I could change the color individually, but with over 50 data points, I'd rather not if I don't have to! Thank you. Hi, If I undserstand you correctly all you need to do is right click on one of the data points and select Format Data Series. You can then set the colour, line weight, pattern etc. as you want. Repeat f...