Looking up address of last not matiching value2019 Community Moderator ElectionApplescript in Excel to find a value and then look for another value in the same rowExcel - Conditional formatting and field name look upWhat is the difference between .text, .value, and .value2?Way to count last values before each zero?Excel - Return multiple matching values from a column, horizontally in one rowHow would I calculate the longest win streak and best/worst days of the week to play?Toggle cell value based on previous cell's valueHow to increment dates by 7 every 7 rows in ExcelLook up and replace partial values in a listLookup last non blank cell associated with a value
Does Mathematica reuse previous computations?
What are substitutions for coconut in curry?
Look at your watch and tell me what time is it. vs Look at your watch and tell me what time it is
How to deal with a cynical class?
How to read the value of this capacitor?
What is the significance behind "40 days" that often appears in the Bible?
Combining an idiom with a metonymy
What options are left, if Britain cannot decide?
Credit cards used everywhere in Singapore or Malaysia?
What's the meaning of “spike” in the context of “adrenaline spike”?
Do the common programs (for example: "ls", "cat") in Linux and BSD come from the same source code?
A Cautionary Suggestion
Unexpected result from ArcLength
Employee lack of ownership
Does Wild Magic Surge trigger off of spells on the Sorcerer spell list, if I learned them from another class?
Why is the President allowed to veto a cancellation of emergency powers?
Hacking a Safe Lock after 3 tries
compactness of a set where am I going wrong
What do Xenomorphs eat in the Alien series?
Error in Twin Prime Conjecture
Who is flying the vertibirds?
How difficult is it to simply disable/disengage the MCAS on Boeing 737 Max 8 & 9 Aircraft?
Sailing the cryptic seas
Why Choose Less Effective Armour Types?
Looking up address of last not matiching value
2019 Community Moderator ElectionApplescript in Excel to find a value and then look for another value in the same rowExcel - Conditional formatting and field name look upWhat is the difference between .text, .value, and .value2?Way to count last values before each zero?Excel - Return multiple matching values from a column, horizontally in one rowHow would I calculate the longest win streak and best/worst days of the week to play?Toggle cell value based on previous cell's valueHow to increment dates by 7 every 7 rows in ExcelLook up and replace partial values in a listLookup last non blank cell associated with a value
I like to know what the timestamp was of the last OUT for every IN (yellow highlight) , but I cannot seem to figure it out.
I have the following list of values:
The output should look something like that:
IN 2019-03-04 09:47:56.5855908 +01:00 2019-03-04 09:48:00.8269434 +01:00
OUT 2019-03-04 09:47:59.9238027 +01:00
OUT 2019-03-04 09:47:59.9258067 +01:00
OUT 2019-03-04 09:48:00.4588377 +01:00
OUT 2019-03-04 09:48:00.8028618 +01:00
OUT 2019-03-04 09:48:00.8269434 +01:00
IN 2018-09-07 10:10:21.8107528 +02:00 2018-09-07 10:11:08.5897285 +02:00
excel lookup
edited Mar 7 at 14:20
Uwe Keim
27.6k32132214
asked Mar 6 at 13:20
GenericUserGenericUser
111115
add a comment |
I like to know what the timestamp was of the last OUT for every IN (yellow highlight) , but I cannot seem to figure it out.
I have the following list of values:
The output should look something like that:
IN 2019-03-04 09:47:56.5855908 +01:00 2019-03-04 09:48:00.8269434 +01:00
OUT 2019-03-04 09:47:59.9238027 +01:00
OUT 2019-03-04 09:47:59.9258067 +01:00
OUT 2019-03-04 09:48:00.4588377 +01:00
OUT 2019-03-04 09:48:00.8028618 +01:00
OUT 2019-03-04 09:48:00.8269434 +01:00
IN 2018-09-07 10:10:21.8107528 +02:00 2018-09-07 10:11:08.5897285 +02:00
excel lookup
edited Mar 7 at 14:20
Uwe Keim
27.6k32132214
asked Mar 6 at 13:20
GenericUserGenericUser
111115
Can you share what you have tried?
– Scott Hunter
Mar 6 at 13:24
1
Can you show what the output should look like?
– Scott Craner
Mar 6 at 14:09
I tried finding the next value bij uesing VLOOKUP and then trying to get the ADDRESS. BY using INDIRECT to get the value of the cell I actually wanted.
– GenericUser
Mar 7 at 12:03
add a comment |
I like to know what the timestamp was of the last OUT for every IN (yellow highlight) , but I cannot seem to figure it out.
I have the following list of values:
The output should look something like that:
IN 2019-03-04 09:47:56.5855908 +01:00 2019-03-04 09:48:00.8269434 +01:00
OUT 2019-03-04 09:47:59.9238027 +01:00
OUT 2019-03-04 09:47:59.9258067 +01:00
OUT 2019-03-04 09:48:00.4588377 +01:00
OUT 2019-03-04 09:48:00.8028618 +01:00
OUT 2019-03-04 09:48:00.8269434 +01:00
IN 2018-09-07 10:10:21.8107528 +02:00 2018-09-07 10:11:08.5897285 +02:00
excel lookup
edited Mar 7 at 14:20
Uwe Keim
27.6k32132214
asked Mar 6 at 13:20
GenericUserGenericUser
111115
I like to know what the timestamp was of the last OUT for every IN (yellow highlight) , but I cannot seem to figure it out.
I have the following list of values:
The output should look something like that:
IN 2019-03-04 09:47:56.5855908 +01:00 2019-03-04 09:48:00.8269434 +01:00
OUT 2019-03-04 09:47:59.9238027 +01:00
OUT 2019-03-04 09:47:59.9258067 +01:00
OUT 2019-03-04 09:48:00.4588377 +01:00
OUT 2019-03-04 09:48:00.8028618 +01:00
OUT 2019-03-04 09:48:00.8269434 +01:00
IN 2018-09-07 10:10:21.8107528 +02:00 2018-09-07 10:11:08.5897285 +02:00
excel lookup
excel lookup
edited Mar 7 at 14:20
Uwe Keim
27.6k32132214
asked Mar 6 at 13:20
GenericUserGenericUser
111115
edited Mar 7 at 14:20
Uwe Keim
27.6k32132214
asked Mar 6 at 13:20
GenericUserGenericUser
111115
edited Mar 7 at 14:20
Uwe Keim
27.6k32132214
edited Mar 7 at 14:20
Uwe Keim
27.6k32132214
edited Mar 7 at 14:20
Uwe Keim
27.6k32132214
27.6k32132214
asked Mar 6 at 13:20
GenericUserGenericUser
111115
asked Mar 6 at 13:20
GenericUserGenericUser
111115
asked Mar 6 at 13:20
GenericUserGenericUser
111115
111115
Can you share what you have tried?
– Scott Hunter
Mar 6 at 13:24
1
Can you show what the output should look like?
– Scott Craner
Mar 6 at 14:09
I tried finding the next value bij uesing VLOOKUP and then trying to get the ADDRESS. BY using INDIRECT to get the value of the cell I actually wanted.
– GenericUser
Mar 7 at 12:03
add a comment |
Can you share what you have tried?
– Scott Hunter
Mar 6 at 13:24
1
Can you show what the output should look like?
– Scott Craner
Mar 6 at 14:09
I tried finding the next value bij uesing VLOOKUP and then trying to get the ADDRESS. BY using INDIRECT to get the value of the cell I actually wanted.
– GenericUser
Mar 7 at 12:03
Can you share what you have tried?
– Scott Hunter
Mar 6 at 13:24
Can you share what you have tried?
– Scott Hunter
Mar 6 at 13:24
1
1
Can you show what the output should look like?
– Scott Craner
Mar 6 at 14:09
Can you show what the output should look like?
– Scott Craner
Mar 6 at 14:09
I tried finding the next value bij uesing VLOOKUP and then trying to get the ADDRESS. BY using INDIRECT to get the value of the cell I actually wanted.
– GenericUser
Mar 7 at 12:03
I tried finding the next value bij uesing VLOOKUP and then trying to get the ADDRESS. BY using INDIRECT to get the value of the cell I actually wanted.
– GenericUser
Mar 7 at 12:03
add a comment |
1 Answer
1
active
oldest
votes
The OUTs you are interested in are those where the value of the IN/OUT column in that row is OUT but the value in the next row is not.
For example, assuming your IN/OUT column starts at A1, you could define conditional formatting for B1 with the following formula:
=AND(A1="OUT",A1<>A2)
to indicate the desired cells, and apply that formatting to the rest of the column.
We could extend that by putting the following in a new column to just show the desired timestamps:
=IF(AND(A1="OUT",A1<>A2),B1,"")
If you change the "" at the end to C2, the desired timestamp will appear next to each IN; it will also appear next to each of the corresponding OUTS (which could be hidden with conditional formatting; or you could have a new column just showing the IN values, and "" for the OUTs).
answered Mar 6 at 13:27
Scott HunterScott Hunter
33.8k74072
Thanks! That was it. :D
– GenericUser
Mar 7 at 14:46
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%2f55024094%2flooking-up-address-of-last-not-matiching-value%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
The OUTs you are interested in are those where the value of the IN/OUT column in that row is OUT but the value in the next row is not.
For example, assuming your IN/OUT column starts at A1, you could define conditional formatting for B1 with the following formula:
=AND(A1="OUT",A1<>A2)
to indicate the desired cells, and apply that formatting to the rest of the column.
We could extend that by putting the following in a new column to just show the desired timestamps:
=IF(AND(A1="OUT",A1<>A2),B1,"")
If you change the "" at the end to C2, the desired timestamp will appear next to each IN; it will also appear next to each of the corresponding OUTS (which could be hidden with conditional formatting; or you could have a new column just showing the IN values, and "" for the OUTs).
answered Mar 6 at 13:27
Scott HunterScott Hunter
33.8k74072
Thanks! That was it. :D
– GenericUser
Mar 7 at 14:46
add a comment |
The OUTs you are interested in are those where the value of the IN/OUT column in that row is OUT but the value in the next row is not.
For example, assuming your IN/OUT column starts at A1, you could define conditional formatting for B1 with the following formula:
=AND(A1="OUT",A1<>A2)
to indicate the desired cells, and apply that formatting to the rest of the column.
We could extend that by putting the following in a new column to just show the desired timestamps:
=IF(AND(A1="OUT",A1<>A2),B1,"")
If you change the "" at the end to C2, the desired timestamp will appear next to each IN; it will also appear next to each of the corresponding OUTS (which could be hidden with conditional formatting; or you could have a new column just showing the IN values, and "" for the OUTs).
answered Mar 6 at 13:27
Scott HunterScott Hunter
33.8k74072
Thanks! That was it. :D
– GenericUser
Mar 7 at 14:46
add a comment |
The OUTs you are interested in are those where the value of the IN/OUT column in that row is OUT but the value in the next row is not.
For example, assuming your IN/OUT column starts at A1, you could define conditional formatting for B1 with the following formula:
=AND(A1="OUT",A1<>A2)
to indicate the desired cells, and apply that formatting to the rest of the column.
We could extend that by putting the following in a new column to just show the desired timestamps:
=IF(AND(A1="OUT",A1<>A2),B1,"")
If you change the "" at the end to C2, the desired timestamp will appear next to each IN; it will also appear next to each of the corresponding OUTS (which could be hidden with conditional formatting; or you could have a new column just showing the IN values, and "" for the OUTs).
answered Mar 6 at 13:27
Scott HunterScott Hunter
33.8k74072
The OUTs you are interested in are those where the value of the IN/OUT column in that row is OUT but the value in the next row is not.
For example, assuming your IN/OUT column starts at A1, you could define conditional formatting for B1 with the following formula:
=AND(A1="OUT",A1<>A2)
to indicate the desired cells, and apply that formatting to the rest of the column.
We could extend that by putting the following in a new column to just show the desired timestamps:
=IF(AND(A1="OUT",A1<>A2),B1,"")
If you change the "" at the end to C2, the desired timestamp will appear next to each IN; it will also appear next to each of the corresponding OUTS (which could be hidden with conditional formatting; or you could have a new column just showing the IN values, and "" for the OUTs).
answered Mar 6 at 13:27
Scott HunterScott Hunter
33.8k74072
edited Mar 7 at 14:17
answered Mar 6 at 13:27
Scott HunterScott Hunter
33.8k74072
answered Mar 6 at 13:27
Scott HunterScott Hunter
33.8k74072
answered Mar 6 at 13:27
Scott HunterScott Hunter
33.8k74072
33.8k74072
Thanks! That was it. :D
– GenericUser
Mar 7 at 14:46
add a comment |
Thanks! That was it. :D
– GenericUser
Mar 7 at 14:46
Thanks! That was it. :D
– GenericUser
Mar 7 at 14:46
Thanks! That was it. :D
– GenericUser
Mar 7 at 14:46
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%2f55024094%2flooking-up-address-of-last-not-matiching-value%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 share what you have tried?
– Scott Hunter
Mar 6 at 13:24
1
Can you show what the output should look like?
– Scott Craner
Mar 6 at 14:09
I tried finding the next value bij uesing VLOOKUP and then trying to get the ADDRESS. BY using INDIRECT to get the value of the cell I actually wanted.
– GenericUser
Mar 7 at 12:03