Fill Sheet1 ID with value of ID from Sheet2
0
im new here and i have a problem with filling data from another sheet.
Sheet1 looks like this:
_|___A___|___B___|___C___|
1| 1 |John |1,35,12|
2| 2 |Derek |45,2,1 |
and so....
Sheet2
_|___A___|___B___|
1| 1 |Hammer |
2| 2 |Nails |
. . .
12| 12 |Car |
. . .
35| 35 |Rope |
. . .
45| 45 |Vase |
i need to replace ID's in Sheet1 with names from Sheet2 (separated by "," or ", ") to get this:
_|___A___|___B___|_______C_________|
1| 1 |John |Hammer,Rope,Car |
2| 2 |Derek |Vase,Nails,Hammer|
and so....
i dont know if its even possible but if is please help me
ty :)
worksheet-function id
asked Jan 13 at 11:03
silver2002silver2002
1
add a comment |
0
im new here and i have a problem with filling data from another sheet.
Sheet1 looks like this:
_|___A___|___B___|___C___|
1| 1 |John |1,35,12|
2| 2 |Derek |45,2,1 |
and so....
Sheet2
_|___A___|___B___|
1| 1 |Hammer |
2| 2 |Nails |
. . .
12| 12 |Car |
. . .
35| 35 |Rope |
. . .
45| 45 |Vase |
i need to replace ID's in Sheet1 with names from Sheet2 (separated by "," or ", ") to get this:
_|___A___|___B___|_______C_________|
1| 1 |John |Hammer,Rope,Car |
2| 2 |Derek |Vase,Nails,Hammer|
and so....
i dont know if its even possible but if is please help me
ty :)
worksheet-function id
asked Jan 13 at 11:03
silver2002silver2002
1
1. What spreadsheet application are you using? 2. Does col C always contain exactly three items? 3. Must the result replace the col C contents on sheet1, or could the solution involve adding a column with the revised content (and hiding col C if needed), or creating a new sheet that looks like your final example?
– fixer1234
Jan 16 at 5:54
1. excel 2007 2. no... some less, some more 3. it can replace sheet1 or create new sheet that looks like final example.
– silver2002
Jan 16 at 19:12
add a comment |
0
0
0
im new here and i have a problem with filling data from another sheet.
Sheet1 looks like this:
_|___A___|___B___|___C___|
1| 1 |John |1,35,12|
2| 2 |Derek |45,2,1 |
and so....
Sheet2
_|___A___|___B___|
1| 1 |Hammer |
2| 2 |Nails |
. . .
12| 12 |Car |
. . .
35| 35 |Rope |
. . .
45| 45 |Vase |
i need to replace ID's in Sheet1 with names from Sheet2 (separated by "," or ", ") to get this:
_|___A___|___B___|_______C_________|
1| 1 |John |Hammer,Rope,Car |
2| 2 |Derek |Vase,Nails,Hammer|
and so....
i dont know if its even possible but if is please help me
ty :)
worksheet-function id
asked Jan 13 at 11:03
silver2002silver2002
1
im new here and i have a problem with filling data from another sheet.
Sheet1 looks like this:
_|___A___|___B___|___C___|
1| 1 |John |1,35,12|
2| 2 |Derek |45,2,1 |
and so....
Sheet2
_|___A___|___B___|
1| 1 |Hammer |
2| 2 |Nails |
. . .
12| 12 |Car |
. . .
35| 35 |Rope |
. . .
45| 45 |Vase |
i need to replace ID's in Sheet1 with names from Sheet2 (separated by "," or ", ") to get this:
_|___A___|___B___|_______C_________|
1| 1 |John |Hammer,Rope,Car |
2| 2 |Derek |Vase,Nails,Hammer|
and so....
i dont know if its even possible but if is please help me
ty :)
worksheet-function id
worksheet-function id
asked Jan 13 at 11:03
silver2002silver2002
1
asked Jan 13 at 11:03
silver2002silver2002
1
asked Jan 13 at 11:03
silver2002silver2002
1
asked Jan 13 at 11:03
silver2002silver2002
1
asked Jan 13 at 11:03
silver2002silver2002
1
1
1. What spreadsheet application are you using? 2. Does col C always contain exactly three items? 3. Must the result replace the col C contents on sheet1, or could the solution involve adding a column with the revised content (and hiding col C if needed), or creating a new sheet that looks like your final example?
– fixer1234
Jan 16 at 5:54
1. excel 2007 2. no... some less, some more 3. it can replace sheet1 or create new sheet that looks like final example.
– silver2002
Jan 16 at 19:12
add a comment |
1. What spreadsheet application are you using? 2. Does col C always contain exactly three items? 3. Must the result replace the col C contents on sheet1, or could the solution involve adding a column with the revised content (and hiding col C if needed), or creating a new sheet that looks like your final example?
– fixer1234
Jan 16 at 5:54
1. excel 2007 2. no... some less, some more 3. it can replace sheet1 or create new sheet that looks like final example.
– silver2002
Jan 16 at 19:12
1. What spreadsheet application are you using? 2. Does col C always contain exactly three items? 3. Must the result replace the col C contents on sheet1, or could the solution involve adding a column with the revised content (and hiding col C if needed), or creating a new sheet that looks like your final example?
– fixer1234
Jan 16 at 5:54
1. What spreadsheet application are you using? 2. Does col C always contain exactly three items? 3. Must the result replace the col C contents on sheet1, or could the solution involve adding a column with the revised content (and hiding col C if needed), or creating a new sheet that looks like your final example?
– fixer1234
Jan 16 at 5:54
1. excel 2007 2. no... some less, some more 3. it can replace sheet1 or create new sheet that looks like final example.
– silver2002
Jan 16 at 19:12
1. excel 2007 2. no... some less, some more 3. it can replace sheet1 or create new sheet that looks like final example.
– silver2002
Jan 16 at 19:12
add a comment |
1 Answer
1
active
oldest
votes
0
add this code to new module in excel:
Function SplitThenFind(cell As String, sourceColumn As Range)
Dim myArray As Variant
Dim element As Variant
Dim result As String
Dim findResult As Range
myArray = Split(cell, ",")
For Each element In myArray
Set findResult = Application.Worksheets(sourceColumn.Worksheet.Index).Range(sourceColumn.Address).Find(element, Lookat:=xlWhole)
If Not (findResult Is Nothing) Then
' with offset you can select the column you need
result = result & findResult.Cells.Offset(0, 1).Value & ","
End If
Next
If Len(result) > 0 Then
result = Left(result, Len(result) - 1)
End If
SplitThenFind = result
End Function
answered Jan 15 at 10:47
AkelmjAkelmj
1
can u please explain me how to do it? i using excel 2007
– silver2002
Jan 16 at 19:08
I add code to excel module as custom function, look at this good reference: link
– Akelmj
Jan 18 at 18:51
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
});
}
});
draft saved
draft discarded
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%2f1393738%2ffill-sheet1-id-with-value-of-id-from-sheet2%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
0
add this code to new module in excel:
Function SplitThenFind(cell As String, sourceColumn As Range)
Dim myArray As Variant
Dim element As Variant
Dim result As String
Dim findResult As Range
myArray = Split(cell, ",")
For Each element In myArray
Set findResult = Application.Worksheets(sourceColumn.Worksheet.Index).Range(sourceColumn.Address).Find(element, Lookat:=xlWhole)
If Not (findResult Is Nothing) Then
' with offset you can select the column you need
result = result & findResult.Cells.Offset(0, 1).Value & ","
End If
Next
If Len(result) > 0 Then
result = Left(result, Len(result) - 1)
End If
SplitThenFind = result
End Function
answered Jan 15 at 10:47
AkelmjAkelmj
1
can u please explain me how to do it? i using excel 2007
– silver2002
Jan 16 at 19:08
I add code to excel module as custom function, look at this good reference: link
– Akelmj
Jan 18 at 18:51
add a comment |
0
add this code to new module in excel:
Function SplitThenFind(cell As String, sourceColumn As Range)
Dim myArray As Variant
Dim element As Variant
Dim result As String
Dim findResult As Range
myArray = Split(cell, ",")
For Each element In myArray
Set findResult = Application.Worksheets(sourceColumn.Worksheet.Index).Range(sourceColumn.Address).Find(element, Lookat:=xlWhole)
If Not (findResult Is Nothing) Then
' with offset you can select the column you need
result = result & findResult.Cells.Offset(0, 1).Value & ","
End If
Next
If Len(result) > 0 Then
result = Left(result, Len(result) - 1)
End If
SplitThenFind = result
End Function
answered Jan 15 at 10:47
AkelmjAkelmj
1
can u please explain me how to do it? i using excel 2007
– silver2002
Jan 16 at 19:08
I add code to excel module as custom function, look at this good reference: link
– Akelmj
Jan 18 at 18:51
add a comment |
0
0
0
add this code to new module in excel:
Function SplitThenFind(cell As String, sourceColumn As Range)
Dim myArray As Variant
Dim element As Variant
Dim result As String
Dim findResult As Range
myArray = Split(cell, ",")
For Each element In myArray
Set findResult = Application.Worksheets(sourceColumn.Worksheet.Index).Range(sourceColumn.Address).Find(element, Lookat:=xlWhole)
If Not (findResult Is Nothing) Then
' with offset you can select the column you need
result = result & findResult.Cells.Offset(0, 1).Value & ","
End If
Next
If Len(result) > 0 Then
result = Left(result, Len(result) - 1)
End If
SplitThenFind = result
End Function
answered Jan 15 at 10:47
AkelmjAkelmj
1
add this code to new module in excel:
Function SplitThenFind(cell As String, sourceColumn As Range)
Dim myArray As Variant
Dim element As Variant
Dim result As String
Dim findResult As Range
myArray = Split(cell, ",")
For Each element In myArray
Set findResult = Application.Worksheets(sourceColumn.Worksheet.Index).Range(sourceColumn.Address).Find(element, Lookat:=xlWhole)
If Not (findResult Is Nothing) Then
' with offset you can select the column you need
result = result & findResult.Cells.Offset(0, 1).Value & ","
End If
Next
If Len(result) > 0 Then
result = Left(result, Len(result) - 1)
End If
SplitThenFind = result
End Function
answered Jan 15 at 10:47
AkelmjAkelmj
1
answered Jan 15 at 10:47
AkelmjAkelmj
1
answered Jan 15 at 10:47
AkelmjAkelmj
1
answered Jan 15 at 10:47
AkelmjAkelmj
1
1
can u please explain me how to do it? i using excel 2007
– silver2002
Jan 16 at 19:08
I add code to excel module as custom function, look at this good reference: link
– Akelmj
Jan 18 at 18:51
add a comment |
can u please explain me how to do it? i using excel 2007
– silver2002
Jan 16 at 19:08
I add code to excel module as custom function, look at this good reference: link
– Akelmj
Jan 18 at 18:51
can u please explain me how to do it? i using excel 2007
– silver2002
Jan 16 at 19:08
can u please explain me how to do it? i using excel 2007
– silver2002
Jan 16 at 19:08
I add code to excel module as custom function, look at this good reference: link
– Akelmj
Jan 18 at 18:51
I add code to excel module as custom function, look at this good reference: link
– Akelmj
Jan 18 at 18:51
add a comment |
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f1393738%2ffill-sheet1-id-with-value-of-id-from-sheet2%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. What spreadsheet application are you using? 2. Does col C always contain exactly three items? 3. Must the result replace the col C contents on sheet1, or could the solution involve adding a column with the revised content (and hiding col C if needed), or creating a new sheet that looks like your final example?
– fixer1234
Jan 16 at 5:54
1. excel 2007 2. no... some less, some more 3. it can replace sheet1 or create new sheet that looks like final example.
– silver2002
Jan 16 at 19:12