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.1k1093137
asked Jan 7 at 19:31
FireChickenFireChicken
61
New contributor
FireChicken is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
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.1k1093137
asked Jan 7 at 19:31
FireChickenFireChicken
61
New contributor
FireChicken is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
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.1k1093137
asked Jan 7 at 19:31
FireChickenFireChicken
61
New contributor
FireChicken is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
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.1k1093137
asked Jan 7 at 19:31
FireChickenFireChicken
61
New contributor
FireChicken is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited Jan 9 at 10:53
JakeGould
31.1k1093137
asked Jan 7 at 19:31
FireChickenFireChicken
61
New contributor
FireChicken is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited Jan 9 at 10:53
JakeGould
31.1k1093137
edited Jan 9 at 10:53
JakeGould
31.1k1093137
edited Jan 9 at 10:53
JakeGould
31.1k1093137
31.1k1093137
asked Jan 7 at 19:31
FireChickenFireChicken
61
New contributor
FireChicken is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked Jan 7 at 19:31
FireChickenFireChicken
61
asked Jan 7 at 19:31
FireChickenFireChicken
61
61
New contributor
FireChicken is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
FireChicken is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
FireChicken is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
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
});
}
});
FireChicken is a new contributor. Be nice, and check out our Code of Conduct.
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 |
FireChicken is a new contributor. Be nice, and check out our Code of Conduct.
FireChicken is a new contributor. Be nice, and check out our Code of Conduct.
FireChicken is a new contributor. Be nice, and check out our Code of Conduct.
FireChicken is a new contributor. Be nice, and check out our Code of Conduct.
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