How to look up the last occurance of one of 4 recurring symbols from a row that includes various other data?
Per the attached image, I am attempting to create a formula that would accomplish the following:
- Look up the most recent of four recurring characters in a row of other text. Screen shot of the four symbols is included in the attached image as well (Circled Digits 1,2,3, and 4 - other characters in row are all text)
- Identify what date-value it is associated with the last symbol in the column header (row 12): Example: the last yellow 2 in cell J13 is associated with 06 Jan 2019
- Calculate and display in cell A13 how many weeks it has been from that date to the current date (A11).
My biggest frustration so far has been how to find the last of a group of repeating symbols (or value). For example, the last Yellow 2 should be J13 but I have only been able to get C13.
microsoft-excel worksheet-function
edited Jan 9 at 10:53
JakeGould
31.4k1096138
asked Jan 7 at 19:31
FireChickenFireChicken
61
add a comment |
Per the attached image, I am attempting to create a formula that would accomplish the following:
- Look up the most recent of four recurring characters in a row of other text. Screen shot of the four symbols is included in the attached image as well (Circled Digits 1,2,3, and 4 - other characters in row are all text)
- Identify what date-value it is associated with the last symbol in the column header (row 12): Example: the last yellow 2 in cell J13 is associated with 06 Jan 2019
- Calculate and display in cell A13 how many weeks it has been from that date to the current date (A11).
My biggest frustration so far has been how to find the last of a group of repeating symbols (or value). For example, the last Yellow 2 should be J13 but I have only been able to get C13.
microsoft-excel worksheet-function
edited Jan 9 at 10:53
JakeGould
31.4k1096138
asked Jan 7 at 19:31
FireChickenFireChicken
61
1
Can you please edit your question to make it clearer? Sample data, expected outcome, what you've tried so far and how it fails to meet expectations would all be useful
– cybernetic.nomad
Jan 7 at 19:34
add a comment |
Per the attached image, I am attempting to create a formula that would accomplish the following:
- Look up the most recent of four recurring characters in a row of other text. Screen shot of the four symbols is included in the attached image as well (Circled Digits 1,2,3, and 4 - other characters in row are all text)
- Identify what date-value it is associated with the last symbol in the column header (row 12): Example: the last yellow 2 in cell J13 is associated with 06 Jan 2019
- Calculate and display in cell A13 how many weeks it has been from that date to the current date (A11).
My biggest frustration so far has been how to find the last of a group of repeating symbols (or value). For example, the last Yellow 2 should be J13 but I have only been able to get C13.
microsoft-excel worksheet-function
edited Jan 9 at 10:53
JakeGould
31.4k1096138
asked Jan 7 at 19:31
FireChickenFireChicken
61
Per the attached image, I am attempting to create a formula that would accomplish the following:
- Look up the most recent of four recurring characters in a row of other text. Screen shot of the four symbols is included in the attached image as well (Circled Digits 1,2,3, and 4 - other characters in row are all text)
- Identify what date-value it is associated with the last symbol in the column header (row 12): Example: the last yellow 2 in cell J13 is associated with 06 Jan 2019
- Calculate and display in cell A13 how many weeks it has been from that date to the current date (A11).
My biggest frustration so far has been how to find the last of a group of repeating symbols (or value). For example, the last Yellow 2 should be J13 but I have only been able to get C13.
microsoft-excel worksheet-function
microsoft-excel worksheet-function
edited Jan 9 at 10:53
JakeGould
31.4k1096138
asked Jan 7 at 19:31
FireChickenFireChicken
61
edited Jan 9 at 10:53
JakeGould
31.4k1096138
asked Jan 7 at 19:31
FireChickenFireChicken
61
edited Jan 9 at 10:53
JakeGould
31.4k1096138
edited Jan 9 at 10:53
JakeGould
31.4k1096138
edited Jan 9 at 10:53
JakeGould
31.4k1096138
31.4k1096138
asked Jan 7 at 19:31
FireChickenFireChicken
61
asked Jan 7 at 19:31
FireChickenFireChicken
61
asked Jan 7 at 19:31
FireChickenFireChicken
61
61
1
Can you please edit your question to make it clearer? Sample data, expected outcome, what you've tried so far and how it fails to meet expectations would all be useful
– cybernetic.nomad
Jan 7 at 19:34
add a comment |
1
Can you please edit your question to make it clearer? Sample data, expected outcome, what you've tried so far and how it fails to meet expectations would all be useful
– cybernetic.nomad
Jan 7 at 19:34
1
1
Can you please edit your question to make it clearer? Sample data, expected outcome, what you've tried so far and how it fails to meet expectations would all be useful
– cybernetic.nomad
Jan 7 at 19:34
Can you please edit your question to make it clearer? Sample data, expected outcome, what you've tried so far and how it fails to meet expectations would all be useful
– cybernetic.nomad
Jan 7 at 19:34
add a comment |
1 Answer
1
active
oldest
votes
One way to achieve this is that will need to find the max column in your range for each different symbol as per cells
Using helper cells for each, find the address for your header cell from the max of the range of columns B3 to K3 like this in my example cell K6 add the formula in L6
=INDIRECT(ADDRESS(2,MAX(IF($B$3:$K$3=$K6,COLUMN($B$3:$K$3)-COLUMN(INDEX($B$3:$K$3,1,1))+2))))
This need to be added as an array i.e. Ctrl+Shift+Enter
repeat for your other cells you wish to match drag down from L6 to L9
then use =MAX(L6:L9) to give you the latest cell date
#### EDIT ####
Taking this solution further with a small tweek, you can then match multiple criteria without helper cells by using or (+) in the if statement.
=INDIRECT(ADDRESS(2,MAX(IF(($B$3:$K$3="❶")+($B$3:$K$3="❷")+($B$3:$K$3="❸")+($B$3:$K$3="❹"),COLUMN($B$3:$K$3)))))
The INDIRECT function returns the date reference from Row 2 as the result of ADDRESS Column returned by the ORed Max If function.
Just remember this still needs to be added as an array i.e. Ctrl+Shift+Enter
#### EDIT 2 ####
Response to Calculate and display in cell A13 how many weeks it has been from that date to the current date (A11). In cell A13 as an array Use DATEIF with Range From To and days "d" as criteria then divide by 7
=DATEDIF(INDIRECT(ADDRESS(2,MAX(IF(($B$3:$K$3="❶")+($B$3:$K$3="❷")+($B$3:$K$3="❸")+($B$3:$K$3="❹"),COLUMN($B$3:$K$3))))),TODAY(),"d")/7
format cell as number or general
answered Jan 8 at 18:13
AntonyAntony
991912
THANK YOU! This was helpful but I was trying to avoid having to use the helper cells for each. After playing around with what you provided here combined with other research online, I eventually got it to work using this: =MAX(INDEX($C$3:$S$3,SUMPRODUCT(MAX(COLUMN($C4:$S4)*("❶"=$C4:$S4))-2)),INDEX($C$3:$S$3,SUMPRODUCT(MAX(COLUMN($C4:$S4)*("❷"=$C4:$S4))-2)),INDEX($C$3:$S$3,SUMPRODUCT(MAX(COLUMN($C4:$S4)*("❸"=$C4:$S4))-2)),INDEX($C$3:$S$3,SUMPRODUCT(MAX(COLUMN($C4:$S4)*("❹"=$C4:$S4))-2)))
– FireChicken
Jan 8 at 20:40
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%2f1391624%2fhow-to-look-up-the-last-occurance-of-one-of-4-recurring-symbols-from-a-row-that%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
One way to achieve this is that will need to find the max column in your range for each different symbol as per cells
Using helper cells for each, find the address for your header cell from the max of the range of columns B3 to K3 like this in my example cell K6 add the formula in L6
=INDIRECT(ADDRESS(2,MAX(IF($B$3:$K$3=$K6,COLUMN($B$3:$K$3)-COLUMN(INDEX($B$3:$K$3,1,1))+2))))
This need to be added as an array i.e. Ctrl+Shift+Enter
repeat for your other cells you wish to match drag down from L6 to L9
then use =MAX(L6:L9) to give you the latest cell date
#### EDIT ####
Taking this solution further with a small tweek, you can then match multiple criteria without helper cells by using or (+) in the if statement.
=INDIRECT(ADDRESS(2,MAX(IF(($B$3:$K$3="❶")+($B$3:$K$3="❷")+($B$3:$K$3="❸")+($B$3:$K$3="❹"),COLUMN($B$3:$K$3)))))
The INDIRECT function returns the date reference from Row 2 as the result of ADDRESS Column returned by the ORed Max If function.
Just remember this still needs to be added as an array i.e. Ctrl+Shift+Enter
#### EDIT 2 ####
Response to Calculate and display in cell A13 how many weeks it has been from that date to the current date (A11). In cell A13 as an array Use DATEIF with Range From To and days "d" as criteria then divide by 7
=DATEDIF(INDIRECT(ADDRESS(2,MAX(IF(($B$3:$K$3="❶")+($B$3:$K$3="❷")+($B$3:$K$3="❸")+($B$3:$K$3="❹"),COLUMN($B$3:$K$3))))),TODAY(),"d")/7
format cell as number or general
answered Jan 8 at 18:13
AntonyAntony
991912
THANK YOU! This was helpful but I was trying to avoid having to use the helper cells for each. After playing around with what you provided here combined with other research online, I eventually got it to work using this: =MAX(INDEX($C$3:$S$3,SUMPRODUCT(MAX(COLUMN($C4:$S4)*("❶"=$C4:$S4))-2)),INDEX($C$3:$S$3,SUMPRODUCT(MAX(COLUMN($C4:$S4)*("❷"=$C4:$S4))-2)),INDEX($C$3:$S$3,SUMPRODUCT(MAX(COLUMN($C4:$S4)*("❸"=$C4:$S4))-2)),INDEX($C$3:$S$3,SUMPRODUCT(MAX(COLUMN($C4:$S4)*("❹"=$C4:$S4))-2)))
– FireChicken
Jan 8 at 20:40
add a comment |
One way to achieve this is that will need to find the max column in your range for each different symbol as per cells
Using helper cells for each, find the address for your header cell from the max of the range of columns B3 to K3 like this in my example cell K6 add the formula in L6
=INDIRECT(ADDRESS(2,MAX(IF($B$3:$K$3=$K6,COLUMN($B$3:$K$3)-COLUMN(INDEX($B$3:$K$3,1,1))+2))))
This need to be added as an array i.e. Ctrl+Shift+Enter
repeat for your other cells you wish to match drag down from L6 to L9
then use =MAX(L6:L9) to give you the latest cell date
#### EDIT ####
Taking this solution further with a small tweek, you can then match multiple criteria without helper cells by using or (+) in the if statement.
=INDIRECT(ADDRESS(2,MAX(IF(($B$3:$K$3="❶")+($B$3:$K$3="❷")+($B$3:$K$3="❸")+($B$3:$K$3="❹"),COLUMN($B$3:$K$3)))))
The INDIRECT function returns the date reference from Row 2 as the result of ADDRESS Column returned by the ORed Max If function.
Just remember this still needs to be added as an array i.e. Ctrl+Shift+Enter
#### EDIT 2 ####
Response to Calculate and display in cell A13 how many weeks it has been from that date to the current date (A11). In cell A13 as an array Use DATEIF with Range From To and days "d" as criteria then divide by 7
=DATEDIF(INDIRECT(ADDRESS(2,MAX(IF(($B$3:$K$3="❶")+($B$3:$K$3="❷")+($B$3:$K$3="❸")+($B$3:$K$3="❹"),COLUMN($B$3:$K$3))))),TODAY(),"d")/7
format cell as number or general
answered Jan 8 at 18:13
AntonyAntony
991912
THANK YOU! This was helpful but I was trying to avoid having to use the helper cells for each. After playing around with what you provided here combined with other research online, I eventually got it to work using this: =MAX(INDEX($C$3:$S$3,SUMPRODUCT(MAX(COLUMN($C4:$S4)*("❶"=$C4:$S4))-2)),INDEX($C$3:$S$3,SUMPRODUCT(MAX(COLUMN($C4:$S4)*("❷"=$C4:$S4))-2)),INDEX($C$3:$S$3,SUMPRODUCT(MAX(COLUMN($C4:$S4)*("❸"=$C4:$S4))-2)),INDEX($C$3:$S$3,SUMPRODUCT(MAX(COLUMN($C4:$S4)*("❹"=$C4:$S4))-2)))
– FireChicken
Jan 8 at 20:40
add a comment |
One way to achieve this is that will need to find the max column in your range for each different symbol as per cells
Using helper cells for each, find the address for your header cell from the max of the range of columns B3 to K3 like this in my example cell K6 add the formula in L6
=INDIRECT(ADDRESS(2,MAX(IF($B$3:$K$3=$K6,COLUMN($B$3:$K$3)-COLUMN(INDEX($B$3:$K$3,1,1))+2))))
This need to be added as an array i.e. Ctrl+Shift+Enter
repeat for your other cells you wish to match drag down from L6 to L9
then use =MAX(L6:L9) to give you the latest cell date
#### EDIT ####
Taking this solution further with a small tweek, you can then match multiple criteria without helper cells by using or (+) in the if statement.
=INDIRECT(ADDRESS(2,MAX(IF(($B$3:$K$3="❶")+($B$3:$K$3="❷")+($B$3:$K$3="❸")+($B$3:$K$3="❹"),COLUMN($B$3:$K$3)))))
The INDIRECT function returns the date reference from Row 2 as the result of ADDRESS Column returned by the ORed Max If function.
Just remember this still needs to be added as an array i.e. Ctrl+Shift+Enter
#### EDIT 2 ####
Response to Calculate and display in cell A13 how many weeks it has been from that date to the current date (A11). In cell A13 as an array Use DATEIF with Range From To and days "d" as criteria then divide by 7
=DATEDIF(INDIRECT(ADDRESS(2,MAX(IF(($B$3:$K$3="❶")+($B$3:$K$3="❷")+($B$3:$K$3="❸")+($B$3:$K$3="❹"),COLUMN($B$3:$K$3))))),TODAY(),"d")/7
format cell as number or general
answered Jan 8 at 18:13
AntonyAntony
991912
One way to achieve this is that will need to find the max column in your range for each different symbol as per cells
Using helper cells for each, find the address for your header cell from the max of the range of columns B3 to K3 like this in my example cell K6 add the formula in L6
=INDIRECT(ADDRESS(2,MAX(IF($B$3:$K$3=$K6,COLUMN($B$3:$K$3)-COLUMN(INDEX($B$3:$K$3,1,1))+2))))
This need to be added as an array i.e. Ctrl+Shift+Enter
repeat for your other cells you wish to match drag down from L6 to L9
then use =MAX(L6:L9) to give you the latest cell date
#### EDIT ####
Taking this solution further with a small tweek, you can then match multiple criteria without helper cells by using or (+) in the if statement.
=INDIRECT(ADDRESS(2,MAX(IF(($B$3:$K$3="❶")+($B$3:$K$3="❷")+($B$3:$K$3="❸")+($B$3:$K$3="❹"),COLUMN($B$3:$K$3)))))
The INDIRECT function returns the date reference from Row 2 as the result of ADDRESS Column returned by the ORed Max If function.
Just remember this still needs to be added as an array i.e. Ctrl+Shift+Enter
#### EDIT 2 ####
Response to Calculate and display in cell A13 how many weeks it has been from that date to the current date (A11). In cell A13 as an array Use DATEIF with Range From To and days "d" as criteria then divide by 7
=DATEDIF(INDIRECT(ADDRESS(2,MAX(IF(($B$3:$K$3="❶")+($B$3:$K$3="❷")+($B$3:$K$3="❸")+($B$3:$K$3="❹"),COLUMN($B$3:$K$3))))),TODAY(),"d")/7
format cell as number or general
answered Jan 8 at 18:13
AntonyAntony
991912
edited Jan 9 at 11:37
answered Jan 8 at 18:13
AntonyAntony
991912
answered Jan 8 at 18:13
AntonyAntony
991912
answered Jan 8 at 18:13
AntonyAntony
991912
991912
THANK YOU! This was helpful but I was trying to avoid having to use the helper cells for each. After playing around with what you provided here combined with other research online, I eventually got it to work using this: =MAX(INDEX($C$3:$S$3,SUMPRODUCT(MAX(COLUMN($C4:$S4)*("❶"=$C4:$S4))-2)),INDEX($C$3:$S$3,SUMPRODUCT(MAX(COLUMN($C4:$S4)*("❷"=$C4:$S4))-2)),INDEX($C$3:$S$3,SUMPRODUCT(MAX(COLUMN($C4:$S4)*("❸"=$C4:$S4))-2)),INDEX($C$3:$S$3,SUMPRODUCT(MAX(COLUMN($C4:$S4)*("❹"=$C4:$S4))-2)))
– FireChicken
Jan 8 at 20:40
add a comment |
THANK YOU! This was helpful but I was trying to avoid having to use the helper cells for each. After playing around with what you provided here combined with other research online, I eventually got it to work using this: =MAX(INDEX($C$3:$S$3,SUMPRODUCT(MAX(COLUMN($C4:$S4)*("❶"=$C4:$S4))-2)),INDEX($C$3:$S$3,SUMPRODUCT(MAX(COLUMN($C4:$S4)*("❷"=$C4:$S4))-2)),INDEX($C$3:$S$3,SUMPRODUCT(MAX(COLUMN($C4:$S4)*("❸"=$C4:$S4))-2)),INDEX($C$3:$S$3,SUMPRODUCT(MAX(COLUMN($C4:$S4)*("❹"=$C4:$S4))-2)))
– FireChicken
Jan 8 at 20:40
THANK YOU! This was helpful but I was trying to avoid having to use the helper cells for each. After playing around with what you provided here combined with other research online, I eventually got it to work using this: =MAX(INDEX($C$3:$S$3,SUMPRODUCT(MAX(COLUMN($C4:$S4)*("❶"=$C4:$S4))-2)),INDEX($C$3:$S$3,SUMPRODUCT(MAX(COLUMN($C4:$S4)*("❷"=$C4:$S4))-2)),INDEX($C$3:$S$3,SUMPRODUCT(MAX(COLUMN($C4:$S4)*("❸"=$C4:$S4))-2)),INDEX($C$3:$S$3,SUMPRODUCT(MAX(COLUMN($C4:$S4)*("❹"=$C4:$S4))-2)))
– FireChicken
Jan 8 at 20:40
THANK YOU! This was helpful but I was trying to avoid having to use the helper cells for each. After playing around with what you provided here combined with other research online, I eventually got it to work using this: =MAX(INDEX($C$3:$S$3,SUMPRODUCT(MAX(COLUMN($C4:$S4)*("❶"=$C4:$S4))-2)),INDEX($C$3:$S$3,SUMPRODUCT(MAX(COLUMN($C4:$S4)*("❷"=$C4:$S4))-2)),INDEX($C$3:$S$3,SUMPRODUCT(MAX(COLUMN($C4:$S4)*("❸"=$C4:$S4))-2)),INDEX($C$3:$S$3,SUMPRODUCT(MAX(COLUMN($C4:$S4)*("❹"=$C4:$S4))-2)))
– FireChicken
Jan 8 at 20:40
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%2f1391624%2fhow-to-look-up-the-last-occurance-of-one-of-4-recurring-symbols-from-a-row-that%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
Can you please edit your question to make it clearer? Sample data, expected outcome, what you've tried so far and how it fails to meet expectations would all be useful
– cybernetic.nomad
Jan 7 at 19:34