Sort multiple rows into one line while combining fields
Please see a basic example of what I'm trying to achieve in the photo, table 2 shows what i am trying to achieve from table 1. Any help would be appreciated. Thanks.
EDITED: As the previous explanation was vague, I have added an image of the actual table and re-written the question below.
ACTUAL TABLE
I need a macro that will run through the cutting list shown and condense the data.
If "L", "W", "T", "Material", "Face Veneers" and "Edge Veneers/Lippings" have the same entry then they require combining. The cutting lists will always vary in quantity of rows depending on the job size.
I need the quantity ("QTY") totalled for the amount of parts combined and the "Part Code" combined so the workshop can label each part during the cutting process.
There will be rows mixed within the total that have individual data, so will not need combining.
The left over rows after being combined will require deleting.
I hope this is clearer! Thanks
vba
asked Nov 23 '18 at 13:11
Liam TreadwellLiam Treadwell
12
add a comment |
Please see a basic example of what I'm trying to achieve in the photo, table 2 shows what i am trying to achieve from table 1. Any help would be appreciated. Thanks.
EDITED: As the previous explanation was vague, I have added an image of the actual table and re-written the question below.
ACTUAL TABLE
I need a macro that will run through the cutting list shown and condense the data.
If "L", "W", "T", "Material", "Face Veneers" and "Edge Veneers/Lippings" have the same entry then they require combining. The cutting lists will always vary in quantity of rows depending on the job size.
I need the quantity ("QTY") totalled for the amount of parts combined and the "Part Code" combined so the workshop can label each part during the cutting process.
There will be rows mixed within the total that have individual data, so will not need combining.
The left over rows after being combined will require deleting.
I hope this is clearer! Thanks
vba
asked Nov 23 '18 at 13:11
Liam TreadwellLiam Treadwell
12
1
Welcome to StackOverflow. Please read the help section on how to ask and then edit your question, as this will help the community better understand your specific issue and provide you with a good answer. Specifically, we need to see what code you've already tried and where you're having issues.
– Graham
Nov 23 '18 at 16:42
Please do not supply externally linked image. Instead, embed there here so they can't get lost.
– Thomas Tempelmann
Nov 26 '18 at 12:37
add a comment |
Please see a basic example of what I'm trying to achieve in the photo, table 2 shows what i am trying to achieve from table 1. Any help would be appreciated. Thanks.
EDITED: As the previous explanation was vague, I have added an image of the actual table and re-written the question below.
ACTUAL TABLE
I need a macro that will run through the cutting list shown and condense the data.
If "L", "W", "T", "Material", "Face Veneers" and "Edge Veneers/Lippings" have the same entry then they require combining. The cutting lists will always vary in quantity of rows depending on the job size.
I need the quantity ("QTY") totalled for the amount of parts combined and the "Part Code" combined so the workshop can label each part during the cutting process.
There will be rows mixed within the total that have individual data, so will not need combining.
The left over rows after being combined will require deleting.
I hope this is clearer! Thanks
vba
asked Nov 23 '18 at 13:11
Liam TreadwellLiam Treadwell
12
Please see a basic example of what I'm trying to achieve in the photo, table 2 shows what i am trying to achieve from table 1. Any help would be appreciated. Thanks.
EDITED: As the previous explanation was vague, I have added an image of the actual table and re-written the question below.
ACTUAL TABLE
I need a macro that will run through the cutting list shown and condense the data.
If "L", "W", "T", "Material", "Face Veneers" and "Edge Veneers/Lippings" have the same entry then they require combining. The cutting lists will always vary in quantity of rows depending on the job size.
I need the quantity ("QTY") totalled for the amount of parts combined and the "Part Code" combined so the workshop can label each part during the cutting process.
There will be rows mixed within the total that have individual data, so will not need combining.
The left over rows after being combined will require deleting.
I hope this is clearer! Thanks
vba
vba
asked Nov 23 '18 at 13:11
Liam TreadwellLiam Treadwell
12
asked Nov 23 '18 at 13:11
Liam TreadwellLiam Treadwell
12
edited Nov 26 '18 at 9:20
Liam Treadwell
asked Nov 23 '18 at 13:11
Liam TreadwellLiam Treadwell
12
asked Nov 23 '18 at 13:11
Liam TreadwellLiam Treadwell
12
asked Nov 23 '18 at 13:11
Liam TreadwellLiam Treadwell
12
12
1
Welcome to StackOverflow. Please read the help section on how to ask and then edit your question, as this will help the community better understand your specific issue and provide you with a good answer. Specifically, we need to see what code you've already tried and where you're having issues.
– Graham
Nov 23 '18 at 16:42
Please do not supply externally linked image. Instead, embed there here so they can't get lost.
– Thomas Tempelmann
Nov 26 '18 at 12:37
add a comment |
1
Welcome to StackOverflow. Please read the help section on how to ask and then edit your question, as this will help the community better understand your specific issue and provide you with a good answer. Specifically, we need to see what code you've already tried and where you're having issues.
– Graham
Nov 23 '18 at 16:42
Please do not supply externally linked image. Instead, embed there here so they can't get lost.
– Thomas Tempelmann
Nov 26 '18 at 12:37
1
1
Welcome to StackOverflow. Please read the help section on how to ask and then edit your question, as this will help the community better understand your specific issue and provide you with a good answer. Specifically, we need to see what code you've already tried and where you're having issues.
– Graham
Nov 23 '18 at 16:42
Welcome to StackOverflow. Please read the help section on how to ask and then edit your question, as this will help the community better understand your specific issue and provide you with a good answer. Specifically, we need to see what code you've already tried and where you're having issues.
– Graham
Nov 23 '18 at 16:42
Please do not supply externally linked image. Instead, embed there here so they can't get lost.
– Thomas Tempelmann
Nov 26 '18 at 12:37
Please do not supply externally linked image. Instead, embed there here so they can't get lost.
– Thomas Tempelmann
Nov 26 '18 at 12:37
add a comment |
1 Answer
1
active
oldest
votes
Whilst you provided a good before -> after screenshot, there are still many uncertainties to me:
- What is the criteria when to group the part codes? Do L,W,T and Material have to be the same or are we simply appending all Part Codes and adding QTY?
- Is the input and output table the same? e.g. shall we delete rows or shall we print the output somewhere else?
The below code will create the desired output for the exact case you've shown, but I'm pretty sure that it is to simplistic for your true case (e.g. it currently only prints one line and adds everything to the first part code). Would be great if you could explain the above
Sub Test()
Dim i As Integer
Dim rInput As Range
Dim vArr As Variant
Set rInput = Range("B5:G8")
vArr = rInput
For i = LBound(vArr) + 1 To UBound(vArr)
If vArr(i, 3) = vArr(1, 3) And _
vArr(i, 4) = vArr(1, 4) And _
vArr(i, 5) = vArr(1, 5) And _
vArr(i, 6) = vArr(1, 6) Then
vArr(1, 1) = vArr(1, 1) & "," & vArr(i, 1)
vArr(1, 2) = vArr(1, 2) + vArr(i, 2)
End If
Next i
rInput.Offset(rInput.Rows.Count + 3).Resize(1, rInput.Columns.Count) = vArr
End Sub
answered Nov 23 '18 at 13:33
Bishonen_PLBishonen_PL
13910
Hi, thank you for coming back so quickly. I would like the rows that are not needed to be deleted as the data will stay in the same table. The criteria required for the part codes to be combined is when L, W, T and Material is the same.
– Liam Treadwell
Nov 23 '18 at 13:37
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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%2fstackoverflow.com%2fquestions%2f53447371%2fsort-multiple-rows-into-one-line-while-combining-fields%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Whilst you provided a good before -> after screenshot, there are still many uncertainties to me:
- What is the criteria when to group the part codes? Do L,W,T and Material have to be the same or are we simply appending all Part Codes and adding QTY?
- Is the input and output table the same? e.g. shall we delete rows or shall we print the output somewhere else?
The below code will create the desired output for the exact case you've shown, but I'm pretty sure that it is to simplistic for your true case (e.g. it currently only prints one line and adds everything to the first part code). Would be great if you could explain the above
Sub Test()
Dim i As Integer
Dim rInput As Range
Dim vArr As Variant
Set rInput = Range("B5:G8")
vArr = rInput
For i = LBound(vArr) + 1 To UBound(vArr)
If vArr(i, 3) = vArr(1, 3) And _
vArr(i, 4) = vArr(1, 4) And _
vArr(i, 5) = vArr(1, 5) And _
vArr(i, 6) = vArr(1, 6) Then
vArr(1, 1) = vArr(1, 1) & "," & vArr(i, 1)
vArr(1, 2) = vArr(1, 2) + vArr(i, 2)
End If
Next i
rInput.Offset(rInput.Rows.Count + 3).Resize(1, rInput.Columns.Count) = vArr
End Sub
answered Nov 23 '18 at 13:33
Bishonen_PLBishonen_PL
13910
Hi, thank you for coming back so quickly. I would like the rows that are not needed to be deleted as the data will stay in the same table. The criteria required for the part codes to be combined is when L, W, T and Material is the same.
– Liam Treadwell
Nov 23 '18 at 13:37
add a comment |
Whilst you provided a good before -> after screenshot, there are still many uncertainties to me:
- What is the criteria when to group the part codes? Do L,W,T and Material have to be the same or are we simply appending all Part Codes and adding QTY?
- Is the input and output table the same? e.g. shall we delete rows or shall we print the output somewhere else?
The below code will create the desired output for the exact case you've shown, but I'm pretty sure that it is to simplistic for your true case (e.g. it currently only prints one line and adds everything to the first part code). Would be great if you could explain the above
Sub Test()
Dim i As Integer
Dim rInput As Range
Dim vArr As Variant
Set rInput = Range("B5:G8")
vArr = rInput
For i = LBound(vArr) + 1 To UBound(vArr)
If vArr(i, 3) = vArr(1, 3) And _
vArr(i, 4) = vArr(1, 4) And _
vArr(i, 5) = vArr(1, 5) And _
vArr(i, 6) = vArr(1, 6) Then
vArr(1, 1) = vArr(1, 1) & "," & vArr(i, 1)
vArr(1, 2) = vArr(1, 2) + vArr(i, 2)
End If
Next i
rInput.Offset(rInput.Rows.Count + 3).Resize(1, rInput.Columns.Count) = vArr
End Sub
answered Nov 23 '18 at 13:33
Bishonen_PLBishonen_PL
13910
Hi, thank you for coming back so quickly. I would like the rows that are not needed to be deleted as the data will stay in the same table. The criteria required for the part codes to be combined is when L, W, T and Material is the same.
– Liam Treadwell
Nov 23 '18 at 13:37
add a comment |
Whilst you provided a good before -> after screenshot, there are still many uncertainties to me:
- What is the criteria when to group the part codes? Do L,W,T and Material have to be the same or are we simply appending all Part Codes and adding QTY?
- Is the input and output table the same? e.g. shall we delete rows or shall we print the output somewhere else?
The below code will create the desired output for the exact case you've shown, but I'm pretty sure that it is to simplistic for your true case (e.g. it currently only prints one line and adds everything to the first part code). Would be great if you could explain the above
Sub Test()
Dim i As Integer
Dim rInput As Range
Dim vArr As Variant
Set rInput = Range("B5:G8")
vArr = rInput
For i = LBound(vArr) + 1 To UBound(vArr)
If vArr(i, 3) = vArr(1, 3) And _
vArr(i, 4) = vArr(1, 4) And _
vArr(i, 5) = vArr(1, 5) And _
vArr(i, 6) = vArr(1, 6) Then
vArr(1, 1) = vArr(1, 1) & "," & vArr(i, 1)
vArr(1, 2) = vArr(1, 2) + vArr(i, 2)
End If
Next i
rInput.Offset(rInput.Rows.Count + 3).Resize(1, rInput.Columns.Count) = vArr
End Sub
answered Nov 23 '18 at 13:33
Bishonen_PLBishonen_PL
13910
Whilst you provided a good before -> after screenshot, there are still many uncertainties to me:
- What is the criteria when to group the part codes? Do L,W,T and Material have to be the same or are we simply appending all Part Codes and adding QTY?
- Is the input and output table the same? e.g. shall we delete rows or shall we print the output somewhere else?
The below code will create the desired output for the exact case you've shown, but I'm pretty sure that it is to simplistic for your true case (e.g. it currently only prints one line and adds everything to the first part code). Would be great if you could explain the above
Sub Test()
Dim i As Integer
Dim rInput As Range
Dim vArr As Variant
Set rInput = Range("B5:G8")
vArr = rInput
For i = LBound(vArr) + 1 To UBound(vArr)
If vArr(i, 3) = vArr(1, 3) And _
vArr(i, 4) = vArr(1, 4) And _
vArr(i, 5) = vArr(1, 5) And _
vArr(i, 6) = vArr(1, 6) Then
vArr(1, 1) = vArr(1, 1) & "," & vArr(i, 1)
vArr(1, 2) = vArr(1, 2) + vArr(i, 2)
End If
Next i
rInput.Offset(rInput.Rows.Count + 3).Resize(1, rInput.Columns.Count) = vArr
End Sub
answered Nov 23 '18 at 13:33
Bishonen_PLBishonen_PL
13910
answered Nov 23 '18 at 13:33
Bishonen_PLBishonen_PL
13910
answered Nov 23 '18 at 13:33
Bishonen_PLBishonen_PL
13910
answered Nov 23 '18 at 13:33
Bishonen_PLBishonen_PL
13910
13910
Hi, thank you for coming back so quickly. I would like the rows that are not needed to be deleted as the data will stay in the same table. The criteria required for the part codes to be combined is when L, W, T and Material is the same.
– Liam Treadwell
Nov 23 '18 at 13:37
add a comment |
Hi, thank you for coming back so quickly. I would like the rows that are not needed to be deleted as the data will stay in the same table. The criteria required for the part codes to be combined is when L, W, T and Material is the same.
– Liam Treadwell
Nov 23 '18 at 13:37
Hi, thank you for coming back so quickly. I would like the rows that are not needed to be deleted as the data will stay in the same table. The criteria required for the part codes to be combined is when L, W, T and Material is the same.
– Liam Treadwell
Nov 23 '18 at 13:37
Hi, thank you for coming back so quickly. I would like the rows that are not needed to be deleted as the data will stay in the same table. The criteria required for the part codes to be combined is when L, W, T and Material is the same.
– Liam Treadwell
Nov 23 '18 at 13:37
add a comment |
Thanks for contributing an answer to Stack Overflow!
- 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%2fstackoverflow.com%2fquestions%2f53447371%2fsort-multiple-rows-into-one-line-while-combining-fields%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
1
Welcome to StackOverflow. Please read the help section on how to ask and then edit your question, as this will help the community better understand your specific issue and provide you with a good answer. Specifically, we need to see what code you've already tried and where you're having issues.
– Graham
Nov 23 '18 at 16:42
Please do not supply externally linked image. Instead, embed there here so they can't get lost.
– Thomas Tempelmann
Nov 26 '18 at 12:37