How to combine data from multiple rows, when certain criterion is metCopy/Pasting data from SQL Server to...
How can I get my players to come to the game session after agreeing to a date?
How to solve a large system of linear algebra?
Eww, those bytes are gross
One Half of Ten; A Riddle
a problem on composition of functions
A starship is travelling at 0.9c and collides with a small rock. Will it leave a clean hole through, or will more happen?
what does しにみえてる mean?
Am I a Rude Number?
Can I become debt free or should I file bankruptcy ? How to manage my debt and finances?
Intern applicant asking for compensation equivalent to that of permanent employee
Publishing research using outdated methods
Can I write a book of my D&D game?
Difference between `vector<int> v;` and `vector<int> v = vector<int>();`
How much mayhem could I cause as a sentient fish?
If I delete my router's history can my ISP still provide it to my parents?
How to remove lines through the legend markers in ListPlot?
My cat mixes up the floors in my building. How can I help him?
Blindfold battle as a gladiatorial spectacle - what are the tactics and communication methods?
Why do members of Congress in committee hearings ask witnesses the same question multiple times?
How to limit sight distance to 1 km
How can animals be objects of ethics without being subjects as well?
How can I deliver in-universe written lore to players without it being dry exposition?
Can I string the D&D Starter Set campaign into another module, keeping the same characters?
Strange Sign on Lab Door
How to combine data from multiple rows, when certain criterion is met
Copy/Pasting data from SQL Server to Excel splits up text into multiple columns?How do I combine data from multiple rows in excel to one cell?Cumulative average using data from multiple rows in an Excel tableHow to combine values from multiple rows into a single row using module?How to remove duplicate rows in Excel 2013 when there are many columns?How to combine values from multiple rows into a single row? Have a module, but need the variables explainingExcel 2010 - how can I combine 2 rows of data into 1 row (multiple columns of data) across numerous rowsCopy data from multiple selectionsExcel: Combine multiple rows but keep column data?Grouping Date/Time by hour in pivot table not working as expected
click to enlarge
I'm trying to massage this Excel document.
It has groups of consecutive rows that are identical
except for the AdminTime
column (Column H
).
When a group of consecutive rows
have the same value for LastRxNo
(Column C
),
then I know that they that are identical except for Column H
.
I want to combine the rows in the group into one row,
with the AdminTime
column containing the AdminTime
values
from all the rows in the group, separated by commas.
Here is a picture of what I want it to look like
click to enlarge
Any ideas? I was thinking an IF function, but I'm not too familiar with Excel to get it to work.
Everything is being pulled from an SQL table
with the Microsoft Query tool in Excel;
here is the query:
microsoft-excel microsoft-excel-2010
|
show 1 more comment
click to enlarge
I'm trying to massage this Excel document.
It has groups of consecutive rows that are identical
except for the AdminTime
column (Column H
).
When a group of consecutive rows
have the same value for LastRxNo
(Column C
),
then I know that they that are identical except for Column H
.
I want to combine the rows in the group into one row,
with the AdminTime
column containing the AdminTime
values
from all the rows in the group, separated by commas.
Here is a picture of what I want it to look like
click to enlarge
Any ideas? I was thinking an IF function, but I'm not too familiar with Excel to get it to work.
Everything is being pulled from an SQL table
with the Microsoft Query tool in Excel;
here is the query:
microsoft-excel microsoft-excel-2010
can you explain 28409 and down, why they don't consolidate? the 7A-7P already is the same?
– datatoo
Jul 3 '12 at 4:24
I should have actually removed the 7A-7P AdminTimes. Everything is being pulled from a SQL table and the (Certain Time through another Time) are medications that are dosed every shift vs twice a day or three times a day. It doesn't matter if they get consolidated. I just need every LastRxNo that are the same to consolidate the AdminTimes into a single row.
– user143018
Jul 3 '12 at 17:19
since it is pulled from a SQL table could you create a query there to do all of this consolidation? or do you need to clear these values in excel first, and then do it?
– datatoo
Jul 3 '12 at 17:28
It could be done either way I guess. I have limited knowledge in SQL. I can post the query though.
– user143018
Jul 3 '12 at 17:32
You can create a function in SQL that will do all of this for you automatically, which may be much easier than a vba solution in excel. if you want to try that approach I can suggest links forums.devshed.com/ms-sql-development-95/… and an access example allenbrowne.com/func-concat.html Once you have a function such as this, the query is not too bad
– datatoo
Jul 3 '12 at 19:09
|
show 1 more comment
click to enlarge
I'm trying to massage this Excel document.
It has groups of consecutive rows that are identical
except for the AdminTime
column (Column H
).
When a group of consecutive rows
have the same value for LastRxNo
(Column C
),
then I know that they that are identical except for Column H
.
I want to combine the rows in the group into one row,
with the AdminTime
column containing the AdminTime
values
from all the rows in the group, separated by commas.
Here is a picture of what I want it to look like
click to enlarge
Any ideas? I was thinking an IF function, but I'm not too familiar with Excel to get it to work.
Everything is being pulled from an SQL table
with the Microsoft Query tool in Excel;
here is the query:
microsoft-excel microsoft-excel-2010
click to enlarge
I'm trying to massage this Excel document.
It has groups of consecutive rows that are identical
except for the AdminTime
column (Column H
).
When a group of consecutive rows
have the same value for LastRxNo
(Column C
),
then I know that they that are identical except for Column H
.
I want to combine the rows in the group into one row,
with the AdminTime
column containing the AdminTime
values
from all the rows in the group, separated by commas.
Here is a picture of what I want it to look like
click to enlarge
Any ideas? I was thinking an IF function, but I'm not too familiar with Excel to get it to work.
Everything is being pulled from an SQL table
with the Microsoft Query tool in Excel;
here is the query:
microsoft-excel microsoft-excel-2010
microsoft-excel microsoft-excel-2010
edited 3 mins ago
Scott
15.9k113990
15.9k113990
asked Jun 28 '12 at 20:17
user143018
can you explain 28409 and down, why they don't consolidate? the 7A-7P already is the same?
– datatoo
Jul 3 '12 at 4:24
I should have actually removed the 7A-7P AdminTimes. Everything is being pulled from a SQL table and the (Certain Time through another Time) are medications that are dosed every shift vs twice a day or three times a day. It doesn't matter if they get consolidated. I just need every LastRxNo that are the same to consolidate the AdminTimes into a single row.
– user143018
Jul 3 '12 at 17:19
since it is pulled from a SQL table could you create a query there to do all of this consolidation? or do you need to clear these values in excel first, and then do it?
– datatoo
Jul 3 '12 at 17:28
It could be done either way I guess. I have limited knowledge in SQL. I can post the query though.
– user143018
Jul 3 '12 at 17:32
You can create a function in SQL that will do all of this for you automatically, which may be much easier than a vba solution in excel. if you want to try that approach I can suggest links forums.devshed.com/ms-sql-development-95/… and an access example allenbrowne.com/func-concat.html Once you have a function such as this, the query is not too bad
– datatoo
Jul 3 '12 at 19:09
|
show 1 more comment
can you explain 28409 and down, why they don't consolidate? the 7A-7P already is the same?
– datatoo
Jul 3 '12 at 4:24
I should have actually removed the 7A-7P AdminTimes. Everything is being pulled from a SQL table and the (Certain Time through another Time) are medications that are dosed every shift vs twice a day or three times a day. It doesn't matter if they get consolidated. I just need every LastRxNo that are the same to consolidate the AdminTimes into a single row.
– user143018
Jul 3 '12 at 17:19
since it is pulled from a SQL table could you create a query there to do all of this consolidation? or do you need to clear these values in excel first, and then do it?
– datatoo
Jul 3 '12 at 17:28
It could be done either way I guess. I have limited knowledge in SQL. I can post the query though.
– user143018
Jul 3 '12 at 17:32
You can create a function in SQL that will do all of this for you automatically, which may be much easier than a vba solution in excel. if you want to try that approach I can suggest links forums.devshed.com/ms-sql-development-95/… and an access example allenbrowne.com/func-concat.html Once you have a function such as this, the query is not too bad
– datatoo
Jul 3 '12 at 19:09
can you explain 28409 and down, why they don't consolidate? the 7A-7P already is the same?
– datatoo
Jul 3 '12 at 4:24
can you explain 28409 and down, why they don't consolidate? the 7A-7P already is the same?
– datatoo
Jul 3 '12 at 4:24
I should have actually removed the 7A-7P AdminTimes. Everything is being pulled from a SQL table and the (Certain Time through another Time) are medications that are dosed every shift vs twice a day or three times a day. It doesn't matter if they get consolidated. I just need every LastRxNo that are the same to consolidate the AdminTimes into a single row.
– user143018
Jul 3 '12 at 17:19
I should have actually removed the 7A-7P AdminTimes. Everything is being pulled from a SQL table and the (Certain Time through another Time) are medications that are dosed every shift vs twice a day or three times a day. It doesn't matter if they get consolidated. I just need every LastRxNo that are the same to consolidate the AdminTimes into a single row.
– user143018
Jul 3 '12 at 17:19
since it is pulled from a SQL table could you create a query there to do all of this consolidation? or do you need to clear these values in excel first, and then do it?
– datatoo
Jul 3 '12 at 17:28
since it is pulled from a SQL table could you create a query there to do all of this consolidation? or do you need to clear these values in excel first, and then do it?
– datatoo
Jul 3 '12 at 17:28
It could be done either way I guess. I have limited knowledge in SQL. I can post the query though.
– user143018
Jul 3 '12 at 17:32
It could be done either way I guess. I have limited knowledge in SQL. I can post the query though.
– user143018
Jul 3 '12 at 17:32
You can create a function in SQL that will do all of this for you automatically, which may be much easier than a vba solution in excel. if you want to try that approach I can suggest links forums.devshed.com/ms-sql-development-95/… and an access example allenbrowne.com/func-concat.html Once you have a function such as this, the query is not too bad
– datatoo
Jul 3 '12 at 19:09
You can create a function in SQL that will do all of this for you automatically, which may be much easier than a vba solution in excel. if you want to try that approach I can suggest links forums.devshed.com/ms-sql-development-95/… and an access example allenbrowne.com/func-concat.html Once you have a function such as this, the query is not too bad
– datatoo
Jul 3 '12 at 19:09
|
show 1 more comment
5 Answers
5
active
oldest
votes
You could just make a pivot table from this data, although rather than being presented in rows it goes down in a column.
i.e. select all the data, Insert>Pivot table, drag PatID, LastRxNo and Admintime into "Row Labels" area of the pivot table, hide any totals or subtotals (I'd post pics but my reputation is high enough on here yet - my first post!)
Creates a pivot table that looks something like this(not sure this will look right in my post):
855
87160
6AM
2PM
10PM
(and so on...)
Alternatively, if you changed the SQL query in your select statment to return the whole name in one cell (Select ......., Patients.PatLName+', '+Patients.PatFname, ........)
you could have the pivot return the whole patient name rather than number, something like this...
Parker, Corde
87160
6AM
2PM
10PM
(and so on...)
It might not be the exact same format you originally asked for, but it seems legible to me (assuming you're using it to check off when meds have been administered?)
add a comment |
Create a column which adds the count to the code
=B5&" "&COUNTIF($B$2:$B5,B5)
then you can lookup each separate entry and concatenate the text the formula in d2 becomes:-
=IF($B2&" "&1=D2, INDEX(C:C,MATCH($B2&" "&1,D:D,0))&" "&IFERROR(INDEX(C:C,MATCH($B2&" "&2,D:D,0)),"")&" "&IFERROR(INDEX(C:C,MATCH($B2&" "&3,D:D,0)),"")&" "&IFERROR(INDEX(C:C,MATCH($B2&" "&4,D:D,0)),""),"")
for each possibility you will need to add a &" "&IFERROR(INDEX(C:C,MATCH($B5&" "&5,D:D,0)),"")
where the &5
become &6
etc. The above formula only stretches to 4 instances.
add a comment |
This is a rather quick and messy macro that leaves lots still to do, but you will get the idea.
It also presumes your original sheet("Sheet1") is sorted by LastRxNo.
If you had the ability to add and call a function in the SQLServer it would be a simpler, less error prone task.
hope this helps get you started. it outputs to a sheet called "summary"
Sub conCatTimes()
Dim rx As String
Dim admTimes As String
Dim tmp As String
Dim i As Long
Dim LastRow As Long
LastRow = Sheets("Sheet1").Range("C" & ActiveSheet.Rows.Count).End(xlUp).Row
rx = ""
i = 1
For Each cell In Sheets("Sheet1").Range("C2:C" & LastRow)
If rx <> cell Then
rx = cell
i = i + 1
tmp = ""
End If
tmp = cell.Offset(0, 11)
admTimes = admTimes + tmp + ", "
If cell.Offset(1, 0) <> rx Then
Sheets("summary").Range("A" & i) = rx
Sheets("summary").Range("B" & i) = Left(admTimes, Len(admTimes) - 2)
admTimes = ""
End If
Next
End Sub
Thanks datatoo for the response. Yeah, it seems like a pretty messy macro. I would have hoped there was an easier way because I seem to be coming across things on multiple lines that I want to make into a single line on another type of report I'm trying to run. Does anybody else have any suggestions/solutions?
– user143018
Jul 6 '12 at 17:14
"things on multiple lines that I want to make into a single line", like what? This only showed copying LastRxNumber but you could easily include everything else. If everything is the same except the adminTimes, this could get you there. But the ultimate easiest way is to create a function on the server itself that exports it the way you need
– datatoo
Jul 6 '12 at 17:20
add a comment |
I would recommend using a macro to copy the contents of the table into an identical table, filter out duplicate values - while ignoring the AdminTime column - and then loop through the original table to find the same values and combine all their AdminTime values in a single string.
In the code below, you'll obviously have to tweak it a bit for your needs, especially where I define the tables and filter by unique values. In addition, I have assumed that AdminTime is the last column in your table which might not be the case.
Option Explicit
Sub combineAdminTimes()
'Declarations
Dim tbl1 As ListObject 'Original table
Dim tbl2 As ListObject 'Abbreviated table
Dim arrColumns() 'Array of column numbers in which to search for duplicate values
Dim rcd1 As ListRow 'Generic list row in original table
Dim rcd2 As ListRow 'Generic list row in abbreviated table
Dim i As Long 'Generic counter
Dim blnMatch As Boolean 'Whether or not the two records match and should be combined
Dim s As String 'String in which to store the combined result
'Initialize
Set tbl1 = Sheet1.ListObjects(1)
Set tbl2 = Sheet2.ListObjects(1)
'Clear the old filtered list
If Not tbl2.DataBodyRange Is Nothing Then tbl2.DataBodyRange.Rows.Delete
'Copy the unfiltered list
tbl1.DataBodyRange.Copy tbl2.Range(2, 1)
'Remove all duplicate values
tbl2.Range.RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, 7), Header:=xlYes
'Loop through records in abbreviated table
For Each rcd2 In tbl2.ListRows
s = ""
'Loop through records in original table
For Each rcd1 In tbl1.ListRows
'Check if any fields in the two records do not match
blnMatch = True
For i = 1 To tbl1.ListColumns.Count - 1
If rcd1.Range(1, i).Value <> rcd2.Range(1, i).Value Then blnMatch = False
Next
'If all matched, then add the time value to the string
If blnMatch Then
If LenB(s) > 0 Then s = s & ", " 'Separate each entry by a comma
s = s & tbl1.ListColumns("AdminTime").Range(rcd1.Range.Row, 1).Value
End If
Next
'Finally, store the combined string in the abbreviated table
tbl2.ListColumns("AdminTime").Range(rcd2.Range.Row, 1).Value = s
Next
End Sub
add a comment |
I would use the Power Query Add-In for this. I think you could achieve this task without writing a line of code.
I would first use the Duplicate Column command to duplicate the AdminTimes column. Then I would use the Pivot command to convert the AdminTimes data into columns:
https://support.office.com/en-US/Article/Pivot-columns-abc9c8da-3be9-44c4-886e-0be331ab387a?ui=en-US&rs=en-US&ad=US
In the Pivot definition I would set the Values column to the "Copy of AdminTimes" column created previously, and the Aggregate Value Function to "Don't Aggregate". This will automatically group by all the other columns.
Then I would use the Merge Columns command to combine the generated new columns into one. I would use a Custom separator ", " (comma, then space), to get the presentation you are after.
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f442783%2fhow-to-combine-data-from-multiple-rows-when-certain-criterion-is-met%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
5 Answers
5
active
oldest
votes
5 Answers
5
active
oldest
votes
active
oldest
votes
active
oldest
votes
You could just make a pivot table from this data, although rather than being presented in rows it goes down in a column.
i.e. select all the data, Insert>Pivot table, drag PatID, LastRxNo and Admintime into "Row Labels" area of the pivot table, hide any totals or subtotals (I'd post pics but my reputation is high enough on here yet - my first post!)
Creates a pivot table that looks something like this(not sure this will look right in my post):
855
87160
6AM
2PM
10PM
(and so on...)
Alternatively, if you changed the SQL query in your select statment to return the whole name in one cell (Select ......., Patients.PatLName+', '+Patients.PatFname, ........)
you could have the pivot return the whole patient name rather than number, something like this...
Parker, Corde
87160
6AM
2PM
10PM
(and so on...)
It might not be the exact same format you originally asked for, but it seems legible to me (assuming you're using it to check off when meds have been administered?)
add a comment |
You could just make a pivot table from this data, although rather than being presented in rows it goes down in a column.
i.e. select all the data, Insert>Pivot table, drag PatID, LastRxNo and Admintime into "Row Labels" area of the pivot table, hide any totals or subtotals (I'd post pics but my reputation is high enough on here yet - my first post!)
Creates a pivot table that looks something like this(not sure this will look right in my post):
855
87160
6AM
2PM
10PM
(and so on...)
Alternatively, if you changed the SQL query in your select statment to return the whole name in one cell (Select ......., Patients.PatLName+', '+Patients.PatFname, ........)
you could have the pivot return the whole patient name rather than number, something like this...
Parker, Corde
87160
6AM
2PM
10PM
(and so on...)
It might not be the exact same format you originally asked for, but it seems legible to me (assuming you're using it to check off when meds have been administered?)
add a comment |
You could just make a pivot table from this data, although rather than being presented in rows it goes down in a column.
i.e. select all the data, Insert>Pivot table, drag PatID, LastRxNo and Admintime into "Row Labels" area of the pivot table, hide any totals or subtotals (I'd post pics but my reputation is high enough on here yet - my first post!)
Creates a pivot table that looks something like this(not sure this will look right in my post):
855
87160
6AM
2PM
10PM
(and so on...)
Alternatively, if you changed the SQL query in your select statment to return the whole name in one cell (Select ......., Patients.PatLName+', '+Patients.PatFname, ........)
you could have the pivot return the whole patient name rather than number, something like this...
Parker, Corde
87160
6AM
2PM
10PM
(and so on...)
It might not be the exact same format you originally asked for, but it seems legible to me (assuming you're using it to check off when meds have been administered?)
You could just make a pivot table from this data, although rather than being presented in rows it goes down in a column.
i.e. select all the data, Insert>Pivot table, drag PatID, LastRxNo and Admintime into "Row Labels" area of the pivot table, hide any totals or subtotals (I'd post pics but my reputation is high enough on here yet - my first post!)
Creates a pivot table that looks something like this(not sure this will look right in my post):
855
87160
6AM
2PM
10PM
(and so on...)
Alternatively, if you changed the SQL query in your select statment to return the whole name in one cell (Select ......., Patients.PatLName+', '+Patients.PatFname, ........)
you could have the pivot return the whole patient name rather than number, something like this...
Parker, Corde
87160
6AM
2PM
10PM
(and so on...)
It might not be the exact same format you originally asked for, but it seems legible to me (assuming you're using it to check off when meds have been administered?)
answered May 16 '14 at 0:15
mmmpretzelmmmpretzel
312
312
add a comment |
add a comment |
Create a column which adds the count to the code
=B5&" "&COUNTIF($B$2:$B5,B5)
then you can lookup each separate entry and concatenate the text the formula in d2 becomes:-
=IF($B2&" "&1=D2, INDEX(C:C,MATCH($B2&" "&1,D:D,0))&" "&IFERROR(INDEX(C:C,MATCH($B2&" "&2,D:D,0)),"")&" "&IFERROR(INDEX(C:C,MATCH($B2&" "&3,D:D,0)),"")&" "&IFERROR(INDEX(C:C,MATCH($B2&" "&4,D:D,0)),""),"")
for each possibility you will need to add a &" "&IFERROR(INDEX(C:C,MATCH($B5&" "&5,D:D,0)),"")
where the &5
become &6
etc. The above formula only stretches to 4 instances.
add a comment |
Create a column which adds the count to the code
=B5&" "&COUNTIF($B$2:$B5,B5)
then you can lookup each separate entry and concatenate the text the formula in d2 becomes:-
=IF($B2&" "&1=D2, INDEX(C:C,MATCH($B2&" "&1,D:D,0))&" "&IFERROR(INDEX(C:C,MATCH($B2&" "&2,D:D,0)),"")&" "&IFERROR(INDEX(C:C,MATCH($B2&" "&3,D:D,0)),"")&" "&IFERROR(INDEX(C:C,MATCH($B2&" "&4,D:D,0)),""),"")
for each possibility you will need to add a &" "&IFERROR(INDEX(C:C,MATCH($B5&" "&5,D:D,0)),"")
where the &5
become &6
etc. The above formula only stretches to 4 instances.
add a comment |
Create a column which adds the count to the code
=B5&" "&COUNTIF($B$2:$B5,B5)
then you can lookup each separate entry and concatenate the text the formula in d2 becomes:-
=IF($B2&" "&1=D2, INDEX(C:C,MATCH($B2&" "&1,D:D,0))&" "&IFERROR(INDEX(C:C,MATCH($B2&" "&2,D:D,0)),"")&" "&IFERROR(INDEX(C:C,MATCH($B2&" "&3,D:D,0)),"")&" "&IFERROR(INDEX(C:C,MATCH($B2&" "&4,D:D,0)),""),"")
for each possibility you will need to add a &" "&IFERROR(INDEX(C:C,MATCH($B5&" "&5,D:D,0)),"")
where the &5
become &6
etc. The above formula only stretches to 4 instances.
Create a column which adds the count to the code
=B5&" "&COUNTIF($B$2:$B5,B5)
then you can lookup each separate entry and concatenate the text the formula in d2 becomes:-
=IF($B2&" "&1=D2, INDEX(C:C,MATCH($B2&" "&1,D:D,0))&" "&IFERROR(INDEX(C:C,MATCH($B2&" "&2,D:D,0)),"")&" "&IFERROR(INDEX(C:C,MATCH($B2&" "&3,D:D,0)),"")&" "&IFERROR(INDEX(C:C,MATCH($B2&" "&4,D:D,0)),""),"")
for each possibility you will need to add a &" "&IFERROR(INDEX(C:C,MATCH($B5&" "&5,D:D,0)),"")
where the &5
become &6
etc. The above formula only stretches to 4 instances.
edited Feb 20 '15 at 16:15
Excellll
11.1k74163
11.1k74163
answered Feb 20 '15 at 16:09
GrahamGraham
611
611
add a comment |
add a comment |
This is a rather quick and messy macro that leaves lots still to do, but you will get the idea.
It also presumes your original sheet("Sheet1") is sorted by LastRxNo.
If you had the ability to add and call a function in the SQLServer it would be a simpler, less error prone task.
hope this helps get you started. it outputs to a sheet called "summary"
Sub conCatTimes()
Dim rx As String
Dim admTimes As String
Dim tmp As String
Dim i As Long
Dim LastRow As Long
LastRow = Sheets("Sheet1").Range("C" & ActiveSheet.Rows.Count).End(xlUp).Row
rx = ""
i = 1
For Each cell In Sheets("Sheet1").Range("C2:C" & LastRow)
If rx <> cell Then
rx = cell
i = i + 1
tmp = ""
End If
tmp = cell.Offset(0, 11)
admTimes = admTimes + tmp + ", "
If cell.Offset(1, 0) <> rx Then
Sheets("summary").Range("A" & i) = rx
Sheets("summary").Range("B" & i) = Left(admTimes, Len(admTimes) - 2)
admTimes = ""
End If
Next
End Sub
Thanks datatoo for the response. Yeah, it seems like a pretty messy macro. I would have hoped there was an easier way because I seem to be coming across things on multiple lines that I want to make into a single line on another type of report I'm trying to run. Does anybody else have any suggestions/solutions?
– user143018
Jul 6 '12 at 17:14
"things on multiple lines that I want to make into a single line", like what? This only showed copying LastRxNumber but you could easily include everything else. If everything is the same except the adminTimes, this could get you there. But the ultimate easiest way is to create a function on the server itself that exports it the way you need
– datatoo
Jul 6 '12 at 17:20
add a comment |
This is a rather quick and messy macro that leaves lots still to do, but you will get the idea.
It also presumes your original sheet("Sheet1") is sorted by LastRxNo.
If you had the ability to add and call a function in the SQLServer it would be a simpler, less error prone task.
hope this helps get you started. it outputs to a sheet called "summary"
Sub conCatTimes()
Dim rx As String
Dim admTimes As String
Dim tmp As String
Dim i As Long
Dim LastRow As Long
LastRow = Sheets("Sheet1").Range("C" & ActiveSheet.Rows.Count).End(xlUp).Row
rx = ""
i = 1
For Each cell In Sheets("Sheet1").Range("C2:C" & LastRow)
If rx <> cell Then
rx = cell
i = i + 1
tmp = ""
End If
tmp = cell.Offset(0, 11)
admTimes = admTimes + tmp + ", "
If cell.Offset(1, 0) <> rx Then
Sheets("summary").Range("A" & i) = rx
Sheets("summary").Range("B" & i) = Left(admTimes, Len(admTimes) - 2)
admTimes = ""
End If
Next
End Sub
Thanks datatoo for the response. Yeah, it seems like a pretty messy macro. I would have hoped there was an easier way because I seem to be coming across things on multiple lines that I want to make into a single line on another type of report I'm trying to run. Does anybody else have any suggestions/solutions?
– user143018
Jul 6 '12 at 17:14
"things on multiple lines that I want to make into a single line", like what? This only showed copying LastRxNumber but you could easily include everything else. If everything is the same except the adminTimes, this could get you there. But the ultimate easiest way is to create a function on the server itself that exports it the way you need
– datatoo
Jul 6 '12 at 17:20
add a comment |
This is a rather quick and messy macro that leaves lots still to do, but you will get the idea.
It also presumes your original sheet("Sheet1") is sorted by LastRxNo.
If you had the ability to add and call a function in the SQLServer it would be a simpler, less error prone task.
hope this helps get you started. it outputs to a sheet called "summary"
Sub conCatTimes()
Dim rx As String
Dim admTimes As String
Dim tmp As String
Dim i As Long
Dim LastRow As Long
LastRow = Sheets("Sheet1").Range("C" & ActiveSheet.Rows.Count).End(xlUp).Row
rx = ""
i = 1
For Each cell In Sheets("Sheet1").Range("C2:C" & LastRow)
If rx <> cell Then
rx = cell
i = i + 1
tmp = ""
End If
tmp = cell.Offset(0, 11)
admTimes = admTimes + tmp + ", "
If cell.Offset(1, 0) <> rx Then
Sheets("summary").Range("A" & i) = rx
Sheets("summary").Range("B" & i) = Left(admTimes, Len(admTimes) - 2)
admTimes = ""
End If
Next
End Sub
This is a rather quick and messy macro that leaves lots still to do, but you will get the idea.
It also presumes your original sheet("Sheet1") is sorted by LastRxNo.
If you had the ability to add and call a function in the SQLServer it would be a simpler, less error prone task.
hope this helps get you started. it outputs to a sheet called "summary"
Sub conCatTimes()
Dim rx As String
Dim admTimes As String
Dim tmp As String
Dim i As Long
Dim LastRow As Long
LastRow = Sheets("Sheet1").Range("C" & ActiveSheet.Rows.Count).End(xlUp).Row
rx = ""
i = 1
For Each cell In Sheets("Sheet1").Range("C2:C" & LastRow)
If rx <> cell Then
rx = cell
i = i + 1
tmp = ""
End If
tmp = cell.Offset(0, 11)
admTimes = admTimes + tmp + ", "
If cell.Offset(1, 0) <> rx Then
Sheets("summary").Range("A" & i) = rx
Sheets("summary").Range("B" & i) = Left(admTimes, Len(admTimes) - 2)
admTimes = ""
End If
Next
End Sub
answered Jul 4 '12 at 1:26
datatoodatatoo
2,8421428
2,8421428
Thanks datatoo for the response. Yeah, it seems like a pretty messy macro. I would have hoped there was an easier way because I seem to be coming across things on multiple lines that I want to make into a single line on another type of report I'm trying to run. Does anybody else have any suggestions/solutions?
– user143018
Jul 6 '12 at 17:14
"things on multiple lines that I want to make into a single line", like what? This only showed copying LastRxNumber but you could easily include everything else. If everything is the same except the adminTimes, this could get you there. But the ultimate easiest way is to create a function on the server itself that exports it the way you need
– datatoo
Jul 6 '12 at 17:20
add a comment |
Thanks datatoo for the response. Yeah, it seems like a pretty messy macro. I would have hoped there was an easier way because I seem to be coming across things on multiple lines that I want to make into a single line on another type of report I'm trying to run. Does anybody else have any suggestions/solutions?
– user143018
Jul 6 '12 at 17:14
"things on multiple lines that I want to make into a single line", like what? This only showed copying LastRxNumber but you could easily include everything else. If everything is the same except the adminTimes, this could get you there. But the ultimate easiest way is to create a function on the server itself that exports it the way you need
– datatoo
Jul 6 '12 at 17:20
Thanks datatoo for the response. Yeah, it seems like a pretty messy macro. I would have hoped there was an easier way because I seem to be coming across things on multiple lines that I want to make into a single line on another type of report I'm trying to run. Does anybody else have any suggestions/solutions?
– user143018
Jul 6 '12 at 17:14
Thanks datatoo for the response. Yeah, it seems like a pretty messy macro. I would have hoped there was an easier way because I seem to be coming across things on multiple lines that I want to make into a single line on another type of report I'm trying to run. Does anybody else have any suggestions/solutions?
– user143018
Jul 6 '12 at 17:14
"things on multiple lines that I want to make into a single line", like what? This only showed copying LastRxNumber but you could easily include everything else. If everything is the same except the adminTimes, this could get you there. But the ultimate easiest way is to create a function on the server itself that exports it the way you need
– datatoo
Jul 6 '12 at 17:20
"things on multiple lines that I want to make into a single line", like what? This only showed copying LastRxNumber but you could easily include everything else. If everything is the same except the adminTimes, this could get you there. But the ultimate easiest way is to create a function on the server itself that exports it the way you need
– datatoo
Jul 6 '12 at 17:20
add a comment |
I would recommend using a macro to copy the contents of the table into an identical table, filter out duplicate values - while ignoring the AdminTime column - and then loop through the original table to find the same values and combine all their AdminTime values in a single string.
In the code below, you'll obviously have to tweak it a bit for your needs, especially where I define the tables and filter by unique values. In addition, I have assumed that AdminTime is the last column in your table which might not be the case.
Option Explicit
Sub combineAdminTimes()
'Declarations
Dim tbl1 As ListObject 'Original table
Dim tbl2 As ListObject 'Abbreviated table
Dim arrColumns() 'Array of column numbers in which to search for duplicate values
Dim rcd1 As ListRow 'Generic list row in original table
Dim rcd2 As ListRow 'Generic list row in abbreviated table
Dim i As Long 'Generic counter
Dim blnMatch As Boolean 'Whether or not the two records match and should be combined
Dim s As String 'String in which to store the combined result
'Initialize
Set tbl1 = Sheet1.ListObjects(1)
Set tbl2 = Sheet2.ListObjects(1)
'Clear the old filtered list
If Not tbl2.DataBodyRange Is Nothing Then tbl2.DataBodyRange.Rows.Delete
'Copy the unfiltered list
tbl1.DataBodyRange.Copy tbl2.Range(2, 1)
'Remove all duplicate values
tbl2.Range.RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, 7), Header:=xlYes
'Loop through records in abbreviated table
For Each rcd2 In tbl2.ListRows
s = ""
'Loop through records in original table
For Each rcd1 In tbl1.ListRows
'Check if any fields in the two records do not match
blnMatch = True
For i = 1 To tbl1.ListColumns.Count - 1
If rcd1.Range(1, i).Value <> rcd2.Range(1, i).Value Then blnMatch = False
Next
'If all matched, then add the time value to the string
If blnMatch Then
If LenB(s) > 0 Then s = s & ", " 'Separate each entry by a comma
s = s & tbl1.ListColumns("AdminTime").Range(rcd1.Range.Row, 1).Value
End If
Next
'Finally, store the combined string in the abbreviated table
tbl2.ListColumns("AdminTime").Range(rcd2.Range.Row, 1).Value = s
Next
End Sub
add a comment |
I would recommend using a macro to copy the contents of the table into an identical table, filter out duplicate values - while ignoring the AdminTime column - and then loop through the original table to find the same values and combine all their AdminTime values in a single string.
In the code below, you'll obviously have to tweak it a bit for your needs, especially where I define the tables and filter by unique values. In addition, I have assumed that AdminTime is the last column in your table which might not be the case.
Option Explicit
Sub combineAdminTimes()
'Declarations
Dim tbl1 As ListObject 'Original table
Dim tbl2 As ListObject 'Abbreviated table
Dim arrColumns() 'Array of column numbers in which to search for duplicate values
Dim rcd1 As ListRow 'Generic list row in original table
Dim rcd2 As ListRow 'Generic list row in abbreviated table
Dim i As Long 'Generic counter
Dim blnMatch As Boolean 'Whether or not the two records match and should be combined
Dim s As String 'String in which to store the combined result
'Initialize
Set tbl1 = Sheet1.ListObjects(1)
Set tbl2 = Sheet2.ListObjects(1)
'Clear the old filtered list
If Not tbl2.DataBodyRange Is Nothing Then tbl2.DataBodyRange.Rows.Delete
'Copy the unfiltered list
tbl1.DataBodyRange.Copy tbl2.Range(2, 1)
'Remove all duplicate values
tbl2.Range.RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, 7), Header:=xlYes
'Loop through records in abbreviated table
For Each rcd2 In tbl2.ListRows
s = ""
'Loop through records in original table
For Each rcd1 In tbl1.ListRows
'Check if any fields in the two records do not match
blnMatch = True
For i = 1 To tbl1.ListColumns.Count - 1
If rcd1.Range(1, i).Value <> rcd2.Range(1, i).Value Then blnMatch = False
Next
'If all matched, then add the time value to the string
If blnMatch Then
If LenB(s) > 0 Then s = s & ", " 'Separate each entry by a comma
s = s & tbl1.ListColumns("AdminTime").Range(rcd1.Range.Row, 1).Value
End If
Next
'Finally, store the combined string in the abbreviated table
tbl2.ListColumns("AdminTime").Range(rcd2.Range.Row, 1).Value = s
Next
End Sub
add a comment |
I would recommend using a macro to copy the contents of the table into an identical table, filter out duplicate values - while ignoring the AdminTime column - and then loop through the original table to find the same values and combine all their AdminTime values in a single string.
In the code below, you'll obviously have to tweak it a bit for your needs, especially where I define the tables and filter by unique values. In addition, I have assumed that AdminTime is the last column in your table which might not be the case.
Option Explicit
Sub combineAdminTimes()
'Declarations
Dim tbl1 As ListObject 'Original table
Dim tbl2 As ListObject 'Abbreviated table
Dim arrColumns() 'Array of column numbers in which to search for duplicate values
Dim rcd1 As ListRow 'Generic list row in original table
Dim rcd2 As ListRow 'Generic list row in abbreviated table
Dim i As Long 'Generic counter
Dim blnMatch As Boolean 'Whether or not the two records match and should be combined
Dim s As String 'String in which to store the combined result
'Initialize
Set tbl1 = Sheet1.ListObjects(1)
Set tbl2 = Sheet2.ListObjects(1)
'Clear the old filtered list
If Not tbl2.DataBodyRange Is Nothing Then tbl2.DataBodyRange.Rows.Delete
'Copy the unfiltered list
tbl1.DataBodyRange.Copy tbl2.Range(2, 1)
'Remove all duplicate values
tbl2.Range.RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, 7), Header:=xlYes
'Loop through records in abbreviated table
For Each rcd2 In tbl2.ListRows
s = ""
'Loop through records in original table
For Each rcd1 In tbl1.ListRows
'Check if any fields in the two records do not match
blnMatch = True
For i = 1 To tbl1.ListColumns.Count - 1
If rcd1.Range(1, i).Value <> rcd2.Range(1, i).Value Then blnMatch = False
Next
'If all matched, then add the time value to the string
If blnMatch Then
If LenB(s) > 0 Then s = s & ", " 'Separate each entry by a comma
s = s & tbl1.ListColumns("AdminTime").Range(rcd1.Range.Row, 1).Value
End If
Next
'Finally, store the combined string in the abbreviated table
tbl2.ListColumns("AdminTime").Range(rcd2.Range.Row, 1).Value = s
Next
End Sub
I would recommend using a macro to copy the contents of the table into an identical table, filter out duplicate values - while ignoring the AdminTime column - and then loop through the original table to find the same values and combine all their AdminTime values in a single string.
In the code below, you'll obviously have to tweak it a bit for your needs, especially where I define the tables and filter by unique values. In addition, I have assumed that AdminTime is the last column in your table which might not be the case.
Option Explicit
Sub combineAdminTimes()
'Declarations
Dim tbl1 As ListObject 'Original table
Dim tbl2 As ListObject 'Abbreviated table
Dim arrColumns() 'Array of column numbers in which to search for duplicate values
Dim rcd1 As ListRow 'Generic list row in original table
Dim rcd2 As ListRow 'Generic list row in abbreviated table
Dim i As Long 'Generic counter
Dim blnMatch As Boolean 'Whether or not the two records match and should be combined
Dim s As String 'String in which to store the combined result
'Initialize
Set tbl1 = Sheet1.ListObjects(1)
Set tbl2 = Sheet2.ListObjects(1)
'Clear the old filtered list
If Not tbl2.DataBodyRange Is Nothing Then tbl2.DataBodyRange.Rows.Delete
'Copy the unfiltered list
tbl1.DataBodyRange.Copy tbl2.Range(2, 1)
'Remove all duplicate values
tbl2.Range.RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, 7), Header:=xlYes
'Loop through records in abbreviated table
For Each rcd2 In tbl2.ListRows
s = ""
'Loop through records in original table
For Each rcd1 In tbl1.ListRows
'Check if any fields in the two records do not match
blnMatch = True
For i = 1 To tbl1.ListColumns.Count - 1
If rcd1.Range(1, i).Value <> rcd2.Range(1, i).Value Then blnMatch = False
Next
'If all matched, then add the time value to the string
If blnMatch Then
If LenB(s) > 0 Then s = s & ", " 'Separate each entry by a comma
s = s & tbl1.ListColumns("AdminTime").Range(rcd1.Range.Row, 1).Value
End If
Next
'Finally, store the combined string in the abbreviated table
tbl2.ListColumns("AdminTime").Range(rcd2.Range.Row, 1).Value = s
Next
End Sub
answered Jul 24 '14 at 16:21
Engineer ToastEngineer Toast
2,9181828
2,9181828
add a comment |
add a comment |
I would use the Power Query Add-In for this. I think you could achieve this task without writing a line of code.
I would first use the Duplicate Column command to duplicate the AdminTimes column. Then I would use the Pivot command to convert the AdminTimes data into columns:
https://support.office.com/en-US/Article/Pivot-columns-abc9c8da-3be9-44c4-886e-0be331ab387a?ui=en-US&rs=en-US&ad=US
In the Pivot definition I would set the Values column to the "Copy of AdminTimes" column created previously, and the Aggregate Value Function to "Don't Aggregate". This will automatically group by all the other columns.
Then I would use the Merge Columns command to combine the generated new columns into one. I would use a Custom separator ", " (comma, then space), to get the presentation you are after.
add a comment |
I would use the Power Query Add-In for this. I think you could achieve this task without writing a line of code.
I would first use the Duplicate Column command to duplicate the AdminTimes column. Then I would use the Pivot command to convert the AdminTimes data into columns:
https://support.office.com/en-US/Article/Pivot-columns-abc9c8da-3be9-44c4-886e-0be331ab387a?ui=en-US&rs=en-US&ad=US
In the Pivot definition I would set the Values column to the "Copy of AdminTimes" column created previously, and the Aggregate Value Function to "Don't Aggregate". This will automatically group by all the other columns.
Then I would use the Merge Columns command to combine the generated new columns into one. I would use a Custom separator ", " (comma, then space), to get the presentation you are after.
add a comment |
I would use the Power Query Add-In for this. I think you could achieve this task without writing a line of code.
I would first use the Duplicate Column command to duplicate the AdminTimes column. Then I would use the Pivot command to convert the AdminTimes data into columns:
https://support.office.com/en-US/Article/Pivot-columns-abc9c8da-3be9-44c4-886e-0be331ab387a?ui=en-US&rs=en-US&ad=US
In the Pivot definition I would set the Values column to the "Copy of AdminTimes" column created previously, and the Aggregate Value Function to "Don't Aggregate". This will automatically group by all the other columns.
Then I would use the Merge Columns command to combine the generated new columns into one. I would use a Custom separator ", " (comma, then space), to get the presentation you are after.
I would use the Power Query Add-In for this. I think you could achieve this task without writing a line of code.
I would first use the Duplicate Column command to duplicate the AdminTimes column. Then I would use the Pivot command to convert the AdminTimes data into columns:
https://support.office.com/en-US/Article/Pivot-columns-abc9c8da-3be9-44c4-886e-0be331ab387a?ui=en-US&rs=en-US&ad=US
In the Pivot definition I would set the Values column to the "Copy of AdminTimes" column created previously, and the Aggregate Value Function to "Don't Aggregate". This will automatically group by all the other columns.
Then I would use the Merge Columns command to combine the generated new columns into one. I would use a Custom separator ", " (comma, then space), to get the presentation you are after.
answered Oct 1 '14 at 4:49
Mike HoneyMike Honey
1,7661511
1,7661511
add a comment |
add a comment |
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f442783%2fhow-to-combine-data-from-multiple-rows-when-certain-criterion-is-met%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
can you explain 28409 and down, why they don't consolidate? the 7A-7P already is the same?
– datatoo
Jul 3 '12 at 4:24
I should have actually removed the 7A-7P AdminTimes. Everything is being pulled from a SQL table and the (Certain Time through another Time) are medications that are dosed every shift vs twice a day or three times a day. It doesn't matter if they get consolidated. I just need every LastRxNo that are the same to consolidate the AdminTimes into a single row.
– user143018
Jul 3 '12 at 17:19
since it is pulled from a SQL table could you create a query there to do all of this consolidation? or do you need to clear these values in excel first, and then do it?
– datatoo
Jul 3 '12 at 17:28
It could be done either way I guess. I have limited knowledge in SQL. I can post the query though.
– user143018
Jul 3 '12 at 17:32
You can create a function in SQL that will do all of this for you automatically, which may be much easier than a vba solution in excel. if you want to try that approach I can suggest links forums.devshed.com/ms-sql-development-95/… and an access example allenbrowne.com/func-concat.html Once you have a function such as this, the query is not too bad
– datatoo
Jul 3 '12 at 19:09