Manipulate multiple excel columns relative to one another
(I first posted this as a 'sort' project. But the data is sorted at the beginning. This is really more of a manipulation to insert blank cells where needed.)
I have multiple columns of time stamped data. The data is already sorted from oldest to newest within each column. It would be fine to invert it to newest to oldest if needed, and I can do that outside of this request. The time stamps in one column may or may not be repeated in the other columns. I need to manipulate the columns' data so that looking across a row, at all columns, the time stamps will always increment as I work down the rows.
Therefore, some data in some columns must me moved down, leaving blank cells in that column. Such as where a column (B in the example) may not have data at "10-30 8:41:10", like another column (A in the example), but it has data at "10-30 8:41:08" and at "10-30 16:51:12". Therefore, the rows that have "10-30 8:41:10" data in other columns must be blank in this column (cells B3 and B4 for example, in the example below).
The example pictures are just a small sample of the actual data. A real example of that needing manipulated has 21 columns. The shortest column has 62 rows of data and the longest column has 462 rows of data. I need to manipulate a new set of data at least 3 times a week. I am pulling this data as a text file and then importing it to Excel. I can delimit as needed to isolate date, time, or whatever is needed prior to manipulate to facilitate. Time is always HH:MM:SS in 24 hour format. The year is not relevant. The data will always be in a short timeframe.
The complexity of the solution is not of primary concern, as the alternative is to shuffle the cells manually. I cannot even complete one manipulation before the next cycle has arrived. The solution does not have to be live/continuous. I will load the data into Excel, and then manipulate it one time. No additional data will be added or modified.
I think VB is the answer, but I've never used it and have gotten bogged down trying. I'm currently looking to see if I can use MIN/MAX on each row, and then VB to insert and shift down in appropriate columns. My other idea is to use RANK and/or 'LARGE/SMALL' to get a number assigned to each cell and then use other functions to copy them to another sheet in correct locations to accomplish the manipulation. No idea if these will work, but trying anything I can think of.
Before manipulation:
After manipulation:
microsoft-excel-2007
edited Dec 17 at 1:41
fixer1234
17.8k144581
asked Dec 15 at 20:19
gwmoto
11
add a comment |
(I first posted this as a 'sort' project. But the data is sorted at the beginning. This is really more of a manipulation to insert blank cells where needed.)
I have multiple columns of time stamped data. The data is already sorted from oldest to newest within each column. It would be fine to invert it to newest to oldest if needed, and I can do that outside of this request. The time stamps in one column may or may not be repeated in the other columns. I need to manipulate the columns' data so that looking across a row, at all columns, the time stamps will always increment as I work down the rows.
Therefore, some data in some columns must me moved down, leaving blank cells in that column. Such as where a column (B in the example) may not have data at "10-30 8:41:10", like another column (A in the example), but it has data at "10-30 8:41:08" and at "10-30 16:51:12". Therefore, the rows that have "10-30 8:41:10" data in other columns must be blank in this column (cells B3 and B4 for example, in the example below).
The example pictures are just a small sample of the actual data. A real example of that needing manipulated has 21 columns. The shortest column has 62 rows of data and the longest column has 462 rows of data. I need to manipulate a new set of data at least 3 times a week. I am pulling this data as a text file and then importing it to Excel. I can delimit as needed to isolate date, time, or whatever is needed prior to manipulate to facilitate. Time is always HH:MM:SS in 24 hour format. The year is not relevant. The data will always be in a short timeframe.
The complexity of the solution is not of primary concern, as the alternative is to shuffle the cells manually. I cannot even complete one manipulation before the next cycle has arrived. The solution does not have to be live/continuous. I will load the data into Excel, and then manipulate it one time. No additional data will be added or modified.
I think VB is the answer, but I've never used it and have gotten bogged down trying. I'm currently looking to see if I can use MIN/MAX on each row, and then VB to insert and shift down in appropriate columns. My other idea is to use RANK and/or 'LARGE/SMALL' to get a number assigned to each cell and then use other functions to copy them to another sheet in correct locations to accomplish the manipulation. No idea if these will work, but trying anything I can think of.
Before manipulation:
After manipulation:
microsoft-excel-2007
edited Dec 17 at 1:41
fixer1234
17.8k144581
asked Dec 15 at 20:19
gwmoto
11
(1) Thank you for showing us the desired output. What do the data look like before being sorted (i.e., what does the input look like)? (2) Are the input data sorted within each column? (3) Do you really have "AM" in some cells and not others? (4) Do you have any cells with "PM"? (5) Do you really have an hour of "8" in some cells and "08" in other cells? Do the cells without the leading zero always have a leading space? (6) Can there be data from multiple years? (Do you expect the solution to be able to realize that "01-01" comes after "12-31"?) … (Cont’d)
– Scott
Dec 16 at 0:29
(Cont’d) … (7) Do you have the times stored as times somewhere? (8) How complicated a solution are you willing to accept? For example, if you need to do this only once (or perhaps once every year), then I would expect you to accept a solution that requires a few steps. If you need to do it once or more per month, I would expect you to demand something simple. Or do you want a solution that works automatically, continually — i.e., you can add data in an "Input" sheet and have the "Output" sheet immediately, automatically updated? … (Cont’d)
– Scott
Dec 16 at 0:29
(Cont’d) … Please do not respond in comments; edit your question to make it clearer and more complete.
– Scott
Dec 16 at 0:29
Scott, thank you for your comments/suggestions. I hope I have addressed all the issues and it is more easily understood now.
– gwmoto
Dec 16 at 23:53
add a comment |
(I first posted this as a 'sort' project. But the data is sorted at the beginning. This is really more of a manipulation to insert blank cells where needed.)
I have multiple columns of time stamped data. The data is already sorted from oldest to newest within each column. It would be fine to invert it to newest to oldest if needed, and I can do that outside of this request. The time stamps in one column may or may not be repeated in the other columns. I need to manipulate the columns' data so that looking across a row, at all columns, the time stamps will always increment as I work down the rows.
Therefore, some data in some columns must me moved down, leaving blank cells in that column. Such as where a column (B in the example) may not have data at "10-30 8:41:10", like another column (A in the example), but it has data at "10-30 8:41:08" and at "10-30 16:51:12". Therefore, the rows that have "10-30 8:41:10" data in other columns must be blank in this column (cells B3 and B4 for example, in the example below).
The example pictures are just a small sample of the actual data. A real example of that needing manipulated has 21 columns. The shortest column has 62 rows of data and the longest column has 462 rows of data. I need to manipulate a new set of data at least 3 times a week. I am pulling this data as a text file and then importing it to Excel. I can delimit as needed to isolate date, time, or whatever is needed prior to manipulate to facilitate. Time is always HH:MM:SS in 24 hour format. The year is not relevant. The data will always be in a short timeframe.
The complexity of the solution is not of primary concern, as the alternative is to shuffle the cells manually. I cannot even complete one manipulation before the next cycle has arrived. The solution does not have to be live/continuous. I will load the data into Excel, and then manipulate it one time. No additional data will be added or modified.
I think VB is the answer, but I've never used it and have gotten bogged down trying. I'm currently looking to see if I can use MIN/MAX on each row, and then VB to insert and shift down in appropriate columns. My other idea is to use RANK and/or 'LARGE/SMALL' to get a number assigned to each cell and then use other functions to copy them to another sheet in correct locations to accomplish the manipulation. No idea if these will work, but trying anything I can think of.
Before manipulation:
After manipulation:
microsoft-excel-2007
edited Dec 17 at 1:41
fixer1234
17.8k144581
asked Dec 15 at 20:19
gwmoto
11
(I first posted this as a 'sort' project. But the data is sorted at the beginning. This is really more of a manipulation to insert blank cells where needed.)
I have multiple columns of time stamped data. The data is already sorted from oldest to newest within each column. It would be fine to invert it to newest to oldest if needed, and I can do that outside of this request. The time stamps in one column may or may not be repeated in the other columns. I need to manipulate the columns' data so that looking across a row, at all columns, the time stamps will always increment as I work down the rows.
Therefore, some data in some columns must me moved down, leaving blank cells in that column. Such as where a column (B in the example) may not have data at "10-30 8:41:10", like another column (A in the example), but it has data at "10-30 8:41:08" and at "10-30 16:51:12". Therefore, the rows that have "10-30 8:41:10" data in other columns must be blank in this column (cells B3 and B4 for example, in the example below).
The example pictures are just a small sample of the actual data. A real example of that needing manipulated has 21 columns. The shortest column has 62 rows of data and the longest column has 462 rows of data. I need to manipulate a new set of data at least 3 times a week. I am pulling this data as a text file and then importing it to Excel. I can delimit as needed to isolate date, time, or whatever is needed prior to manipulate to facilitate. Time is always HH:MM:SS in 24 hour format. The year is not relevant. The data will always be in a short timeframe.
The complexity of the solution is not of primary concern, as the alternative is to shuffle the cells manually. I cannot even complete one manipulation before the next cycle has arrived. The solution does not have to be live/continuous. I will load the data into Excel, and then manipulate it one time. No additional data will be added or modified.
I think VB is the answer, but I've never used it and have gotten bogged down trying. I'm currently looking to see if I can use MIN/MAX on each row, and then VB to insert and shift down in appropriate columns. My other idea is to use RANK and/or 'LARGE/SMALL' to get a number assigned to each cell and then use other functions to copy them to another sheet in correct locations to accomplish the manipulation. No idea if these will work, but trying anything I can think of.
Before manipulation:
After manipulation:
microsoft-excel-2007
microsoft-excel-2007
edited Dec 17 at 1:41
fixer1234
17.8k144581
asked Dec 15 at 20:19
gwmoto
11
edited Dec 17 at 1:41
fixer1234
17.8k144581
asked Dec 15 at 20:19
gwmoto
11
edited Dec 17 at 1:41
fixer1234
17.8k144581
edited Dec 17 at 1:41
fixer1234
17.8k144581
edited Dec 17 at 1:41
fixer1234
17.8k144581
17.8k144581
asked Dec 15 at 20:19
gwmoto
11
asked Dec 15 at 20:19
gwmoto
11
asked Dec 15 at 20:19
gwmoto
11
11
(1) Thank you for showing us the desired output. What do the data look like before being sorted (i.e., what does the input look like)? (2) Are the input data sorted within each column? (3) Do you really have "AM" in some cells and not others? (4) Do you have any cells with "PM"? (5) Do you really have an hour of "8" in some cells and "08" in other cells? Do the cells without the leading zero always have a leading space? (6) Can there be data from multiple years? (Do you expect the solution to be able to realize that "01-01" comes after "12-31"?) … (Cont’d)
– Scott
Dec 16 at 0:29
(Cont’d) … (7) Do you have the times stored as times somewhere? (8) How complicated a solution are you willing to accept? For example, if you need to do this only once (or perhaps once every year), then I would expect you to accept a solution that requires a few steps. If you need to do it once or more per month, I would expect you to demand something simple. Or do you want a solution that works automatically, continually — i.e., you can add data in an "Input" sheet and have the "Output" sheet immediately, automatically updated? … (Cont’d)
– Scott
Dec 16 at 0:29
(Cont’d) … Please do not respond in comments; edit your question to make it clearer and more complete.
– Scott
Dec 16 at 0:29
Scott, thank you for your comments/suggestions. I hope I have addressed all the issues and it is more easily understood now.
– gwmoto
Dec 16 at 23:53
add a comment |
(1) Thank you for showing us the desired output. What do the data look like before being sorted (i.e., what does the input look like)? (2) Are the input data sorted within each column? (3) Do you really have "AM" in some cells and not others? (4) Do you have any cells with "PM"? (5) Do you really have an hour of "8" in some cells and "08" in other cells? Do the cells without the leading zero always have a leading space? (6) Can there be data from multiple years? (Do you expect the solution to be able to realize that "01-01" comes after "12-31"?) … (Cont’d)
– Scott
Dec 16 at 0:29
(Cont’d) … (7) Do you have the times stored as times somewhere? (8) How complicated a solution are you willing to accept? For example, if you need to do this only once (or perhaps once every year), then I would expect you to accept a solution that requires a few steps. If you need to do it once or more per month, I would expect you to demand something simple. Or do you want a solution that works automatically, continually — i.e., you can add data in an "Input" sheet and have the "Output" sheet immediately, automatically updated? … (Cont’d)
– Scott
Dec 16 at 0:29
(Cont’d) … Please do not respond in comments; edit your question to make it clearer and more complete.
– Scott
Dec 16 at 0:29
Scott, thank you for your comments/suggestions. I hope I have addressed all the issues and it is more easily understood now.
– gwmoto
Dec 16 at 23:53
(1) Thank you for showing us the desired output. What do the data look like before being sorted (i.e., what does the input look like)? (2) Are the input data sorted within each column? (3) Do you really have "AM" in some cells and not others? (4) Do you have any cells with "PM"? (5) Do you really have an hour of "8" in some cells and "08" in other cells? Do the cells without the leading zero always have a leading space? (6) Can there be data from multiple years? (Do you expect the solution to be able to realize that "01-01" comes after "12-31"?) … (Cont’d)
– Scott
Dec 16 at 0:29
(1) Thank you for showing us the desired output. What do the data look like before being sorted (i.e., what does the input look like)? (2) Are the input data sorted within each column? (3) Do you really have "AM" in some cells and not others? (4) Do you have any cells with "PM"? (5) Do you really have an hour of "8" in some cells and "08" in other cells? Do the cells without the leading zero always have a leading space? (6) Can there be data from multiple years? (Do you expect the solution to be able to realize that "01-01" comes after "12-31"?) … (Cont’d)
– Scott
Dec 16 at 0:29
(Cont’d) … (7) Do you have the times stored as times somewhere? (8) How complicated a solution are you willing to accept? For example, if you need to do this only once (or perhaps once every year), then I would expect you to accept a solution that requires a few steps. If you need to do it once or more per month, I would expect you to demand something simple. Or do you want a solution that works automatically, continually — i.e., you can add data in an "Input" sheet and have the "Output" sheet immediately, automatically updated? … (Cont’d)
– Scott
Dec 16 at 0:29
(Cont’d) … (7) Do you have the times stored as times somewhere? (8) How complicated a solution are you willing to accept? For example, if you need to do this only once (or perhaps once every year), then I would expect you to accept a solution that requires a few steps. If you need to do it once or more per month, I would expect you to demand something simple. Or do you want a solution that works automatically, continually — i.e., you can add data in an "Input" sheet and have the "Output" sheet immediately, automatically updated? … (Cont’d)
– Scott
Dec 16 at 0:29
(Cont’d) … Please do not respond in comments; edit your question to make it clearer and more complete.
– Scott
Dec 16 at 0:29
(Cont’d) … Please do not respond in comments; edit your question to make it clearer and more complete.
– Scott
Dec 16 at 0:29
Scott, thank you for your comments/suggestions. I hope I have addressed all the issues and it is more easily understood now.
– gwmoto
Dec 16 at 23:53
Scott, thank you for your comments/suggestions. I hope I have addressed all the issues and it is more easily understood now.
– gwmoto
Dec 16 at 23:53
add a comment |
1 Answer
1
active
oldest
votes
Assuming 100 lines of data..
put :
E1 ----> =IFERROR(DATE(2018,MID(A1,1,2),MID(A1,4,2))+TIME(MID(A1,7,2),MID(A1,10,2),MID(A1,13,2)),"")
and drag to G1, then
I1 ----> =IFERROR(RANK(E1,$E:$G,1),"")
and drag to K1, then
M1 ----> 1 (type manually)
M2 ----> =IF((ROW()-MATCH($M1,M:M,0)+1)<=MAX(COUNTIF($I$1:$I$100,$M1),COUNTIF($J$1:$J$100,$M1),COUNTIF($K$1:$K$100,$M1)),M1,M1+COUNTIF($I$1:$K$100,$M1))
then
P1 ----> =IF(IF(ROW()=1,TRUE,$M1<>OFFSET($M1,-1,0)),IFERROR(INDEX(A$1:A$100,MATCH($M1,I$1:I$100,0)),""),IF($M1>MAX($I$1:$K$100),"",IF(COUNTIF(I$1:I$100,$M1)<=1,"",IF(COUNTIF(INDIRECT("M1:M"&ROW()),$M1)<=COUNTIF(I$1:I$100,$M1),INDEX(A$1:A$100,MATCH($M1,I$1:I$100,0)+COUNTIF(INDIRECT("M1:M"&ROW()),$M1)-1),""))))
and drag to R1.
Then drag E1:K1 to E1:K1 , M2 to line M140 and P1:R1 to P130:R130 .
That shall do it.
Behind the scene :
- column E-G : use mid() to extract date and time text & use
date()+time to convert that text to excel time/date value. - column I-K : use rank() to get the time in sequence, it'll have the
same number if it is a tie. - column M : use countif() to reserve and list "time in sequence" per
row. - column P-R : [ ok.. have to be honest on this.. its a bit tough.. but
DO tell me if you don't it (you are the owner of the quest anyway..)
. hope you feel better understanding it so that you can tweak it later/use it better. ( : ]
column P-R :
For clarity: you may copy the formula.. put it in notepad++ or notepad, and break (tab/enter) it .
1st : if this is first "time in sequence" occurrence in column M, then use index() on column A:C, using match on I:K . or this part IFERROR(INDEX(A$1:A$100,MATCH($M4,I$1:I$100,0)),"")
2nd : if this is not the first occurrence in column M, then use check using countif() in each column A:C to see if there is more match for the same "date/time".
If no more, display blank.
If there is more of same "date/time" found in the respective column, display it using use index() on column A:C, using match on I:K, that was offset()-ed by the countif() for current row column M. aka this part : INDEX(A$1:A$100,MATCH($M1,I$1:I$100,0)+COUNTIF(INDIRECT("M1:M"&ROW()),$M1)-1)
answered Dec 20 at 10:37
p._phidot_
56429
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%2f1384897%2fmanipulate-multiple-excel-columns-relative-to-one-another%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
Assuming 100 lines of data..
put :
E1 ----> =IFERROR(DATE(2018,MID(A1,1,2),MID(A1,4,2))+TIME(MID(A1,7,2),MID(A1,10,2),MID(A1,13,2)),"")
and drag to G1, then
I1 ----> =IFERROR(RANK(E1,$E:$G,1),"")
and drag to K1, then
M1 ----> 1 (type manually)
M2 ----> =IF((ROW()-MATCH($M1,M:M,0)+1)<=MAX(COUNTIF($I$1:$I$100,$M1),COUNTIF($J$1:$J$100,$M1),COUNTIF($K$1:$K$100,$M1)),M1,M1+COUNTIF($I$1:$K$100,$M1))
then
P1 ----> =IF(IF(ROW()=1,TRUE,$M1<>OFFSET($M1,-1,0)),IFERROR(INDEX(A$1:A$100,MATCH($M1,I$1:I$100,0)),""),IF($M1>MAX($I$1:$K$100),"",IF(COUNTIF(I$1:I$100,$M1)<=1,"",IF(COUNTIF(INDIRECT("M1:M"&ROW()),$M1)<=COUNTIF(I$1:I$100,$M1),INDEX(A$1:A$100,MATCH($M1,I$1:I$100,0)+COUNTIF(INDIRECT("M1:M"&ROW()),$M1)-1),""))))
and drag to R1.
Then drag E1:K1 to E1:K1 , M2 to line M140 and P1:R1 to P130:R130 .
That shall do it.
Behind the scene :
- column E-G : use mid() to extract date and time text & use
date()+time to convert that text to excel time/date value. - column I-K : use rank() to get the time in sequence, it'll have the
same number if it is a tie. - column M : use countif() to reserve and list "time in sequence" per
row. - column P-R : [ ok.. have to be honest on this.. its a bit tough.. but
DO tell me if you don't it (you are the owner of the quest anyway..)
. hope you feel better understanding it so that you can tweak it later/use it better. ( : ]
column P-R :
For clarity: you may copy the formula.. put it in notepad++ or notepad, and break (tab/enter) it .
1st : if this is first "time in sequence" occurrence in column M, then use index() on column A:C, using match on I:K . or this part IFERROR(INDEX(A$1:A$100,MATCH($M4,I$1:I$100,0)),"")
2nd : if this is not the first occurrence in column M, then use check using countif() in each column A:C to see if there is more match for the same "date/time".
If no more, display blank.
If there is more of same "date/time" found in the respective column, display it using use index() on column A:C, using match on I:K, that was offset()-ed by the countif() for current row column M. aka this part : INDEX(A$1:A$100,MATCH($M1,I$1:I$100,0)+COUNTIF(INDIRECT("M1:M"&ROW()),$M1)-1)
answered Dec 20 at 10:37
p._phidot_
56429
add a comment |
Assuming 100 lines of data..
put :
E1 ----> =IFERROR(DATE(2018,MID(A1,1,2),MID(A1,4,2))+TIME(MID(A1,7,2),MID(A1,10,2),MID(A1,13,2)),"")
and drag to G1, then
I1 ----> =IFERROR(RANK(E1,$E:$G,1),"")
and drag to K1, then
M1 ----> 1 (type manually)
M2 ----> =IF((ROW()-MATCH($M1,M:M,0)+1)<=MAX(COUNTIF($I$1:$I$100,$M1),COUNTIF($J$1:$J$100,$M1),COUNTIF($K$1:$K$100,$M1)),M1,M1+COUNTIF($I$1:$K$100,$M1))
then
P1 ----> =IF(IF(ROW()=1,TRUE,$M1<>OFFSET($M1,-1,0)),IFERROR(INDEX(A$1:A$100,MATCH($M1,I$1:I$100,0)),""),IF($M1>MAX($I$1:$K$100),"",IF(COUNTIF(I$1:I$100,$M1)<=1,"",IF(COUNTIF(INDIRECT("M1:M"&ROW()),$M1)<=COUNTIF(I$1:I$100,$M1),INDEX(A$1:A$100,MATCH($M1,I$1:I$100,0)+COUNTIF(INDIRECT("M1:M"&ROW()),$M1)-1),""))))
and drag to R1.
Then drag E1:K1 to E1:K1 , M2 to line M140 and P1:R1 to P130:R130 .
That shall do it.
Behind the scene :
- column E-G : use mid() to extract date and time text & use
date()+time to convert that text to excel time/date value. - column I-K : use rank() to get the time in sequence, it'll have the
same number if it is a tie. - column M : use countif() to reserve and list "time in sequence" per
row. - column P-R : [ ok.. have to be honest on this.. its a bit tough.. but
DO tell me if you don't it (you are the owner of the quest anyway..)
. hope you feel better understanding it so that you can tweak it later/use it better. ( : ]
column P-R :
For clarity: you may copy the formula.. put it in notepad++ or notepad, and break (tab/enter) it .
1st : if this is first "time in sequence" occurrence in column M, then use index() on column A:C, using match on I:K . or this part IFERROR(INDEX(A$1:A$100,MATCH($M4,I$1:I$100,0)),"")
2nd : if this is not the first occurrence in column M, then use check using countif() in each column A:C to see if there is more match for the same "date/time".
If no more, display blank.
If there is more of same "date/time" found in the respective column, display it using use index() on column A:C, using match on I:K, that was offset()-ed by the countif() for current row column M. aka this part : INDEX(A$1:A$100,MATCH($M1,I$1:I$100,0)+COUNTIF(INDIRECT("M1:M"&ROW()),$M1)-1)
answered Dec 20 at 10:37
p._phidot_
56429
add a comment |
Assuming 100 lines of data..
put :
E1 ----> =IFERROR(DATE(2018,MID(A1,1,2),MID(A1,4,2))+TIME(MID(A1,7,2),MID(A1,10,2),MID(A1,13,2)),"")
and drag to G1, then
I1 ----> =IFERROR(RANK(E1,$E:$G,1),"")
and drag to K1, then
M1 ----> 1 (type manually)
M2 ----> =IF((ROW()-MATCH($M1,M:M,0)+1)<=MAX(COUNTIF($I$1:$I$100,$M1),COUNTIF($J$1:$J$100,$M1),COUNTIF($K$1:$K$100,$M1)),M1,M1+COUNTIF($I$1:$K$100,$M1))
then
P1 ----> =IF(IF(ROW()=1,TRUE,$M1<>OFFSET($M1,-1,0)),IFERROR(INDEX(A$1:A$100,MATCH($M1,I$1:I$100,0)),""),IF($M1>MAX($I$1:$K$100),"",IF(COUNTIF(I$1:I$100,$M1)<=1,"",IF(COUNTIF(INDIRECT("M1:M"&ROW()),$M1)<=COUNTIF(I$1:I$100,$M1),INDEX(A$1:A$100,MATCH($M1,I$1:I$100,0)+COUNTIF(INDIRECT("M1:M"&ROW()),$M1)-1),""))))
and drag to R1.
Then drag E1:K1 to E1:K1 , M2 to line M140 and P1:R1 to P130:R130 .
That shall do it.
Behind the scene :
- column E-G : use mid() to extract date and time text & use
date()+time to convert that text to excel time/date value. - column I-K : use rank() to get the time in sequence, it'll have the
same number if it is a tie. - column M : use countif() to reserve and list "time in sequence" per
row. - column P-R : [ ok.. have to be honest on this.. its a bit tough.. but
DO tell me if you don't it (you are the owner of the quest anyway..)
. hope you feel better understanding it so that you can tweak it later/use it better. ( : ]
column P-R :
For clarity: you may copy the formula.. put it in notepad++ or notepad, and break (tab/enter) it .
1st : if this is first "time in sequence" occurrence in column M, then use index() on column A:C, using match on I:K . or this part IFERROR(INDEX(A$1:A$100,MATCH($M4,I$1:I$100,0)),"")
2nd : if this is not the first occurrence in column M, then use check using countif() in each column A:C to see if there is more match for the same "date/time".
If no more, display blank.
If there is more of same "date/time" found in the respective column, display it using use index() on column A:C, using match on I:K, that was offset()-ed by the countif() for current row column M. aka this part : INDEX(A$1:A$100,MATCH($M1,I$1:I$100,0)+COUNTIF(INDIRECT("M1:M"&ROW()),$M1)-1)
answered Dec 20 at 10:37
p._phidot_
56429
Assuming 100 lines of data..
put :
E1 ----> =IFERROR(DATE(2018,MID(A1,1,2),MID(A1,4,2))+TIME(MID(A1,7,2),MID(A1,10,2),MID(A1,13,2)),"")
and drag to G1, then
I1 ----> =IFERROR(RANK(E1,$E:$G,1),"")
and drag to K1, then
M1 ----> 1 (type manually)
M2 ----> =IF((ROW()-MATCH($M1,M:M,0)+1)<=MAX(COUNTIF($I$1:$I$100,$M1),COUNTIF($J$1:$J$100,$M1),COUNTIF($K$1:$K$100,$M1)),M1,M1+COUNTIF($I$1:$K$100,$M1))
then
P1 ----> =IF(IF(ROW()=1,TRUE,$M1<>OFFSET($M1,-1,0)),IFERROR(INDEX(A$1:A$100,MATCH($M1,I$1:I$100,0)),""),IF($M1>MAX($I$1:$K$100),"",IF(COUNTIF(I$1:I$100,$M1)<=1,"",IF(COUNTIF(INDIRECT("M1:M"&ROW()),$M1)<=COUNTIF(I$1:I$100,$M1),INDEX(A$1:A$100,MATCH($M1,I$1:I$100,0)+COUNTIF(INDIRECT("M1:M"&ROW()),$M1)-1),""))))
and drag to R1.
Then drag E1:K1 to E1:K1 , M2 to line M140 and P1:R1 to P130:R130 .
That shall do it.
Behind the scene :
- column E-G : use mid() to extract date and time text & use
date()+time to convert that text to excel time/date value. - column I-K : use rank() to get the time in sequence, it'll have the
same number if it is a tie. - column M : use countif() to reserve and list "time in sequence" per
row. - column P-R : [ ok.. have to be honest on this.. its a bit tough.. but
DO tell me if you don't it (you are the owner of the quest anyway..)
. hope you feel better understanding it so that you can tweak it later/use it better. ( : ]
column P-R :
For clarity: you may copy the formula.. put it in notepad++ or notepad, and break (tab/enter) it .
1st : if this is first "time in sequence" occurrence in column M, then use index() on column A:C, using match on I:K . or this part IFERROR(INDEX(A$1:A$100,MATCH($M4,I$1:I$100,0)),"")
2nd : if this is not the first occurrence in column M, then use check using countif() in each column A:C to see if there is more match for the same "date/time".
If no more, display blank.
If there is more of same "date/time" found in the respective column, display it using use index() on column A:C, using match on I:K, that was offset()-ed by the countif() for current row column M. aka this part : INDEX(A$1:A$100,MATCH($M1,I$1:I$100,0)+COUNTIF(INDIRECT("M1:M"&ROW()),$M1)-1)
answered Dec 20 at 10:37
p._phidot_
56429
answered Dec 20 at 10:37
p._phidot_
56429
answered Dec 20 at 10:37
p._phidot_
56429
answered Dec 20 at 10:37
p._phidot_
56429
56429
add a comment |
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2f1384897%2fmanipulate-multiple-excel-columns-relative-to-one-another%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) Thank you for showing us the desired output. What do the data look like before being sorted (i.e., what does the input look like)? (2) Are the input data sorted within each column? (3) Do you really have "AM" in some cells and not others? (4) Do you have any cells with "PM"? (5) Do you really have an hour of "8" in some cells and "08" in other cells? Do the cells without the leading zero always have a leading space? (6) Can there be data from multiple years? (Do you expect the solution to be able to realize that "01-01" comes after "12-31"?) … (Cont’d)
– Scott
Dec 16 at 0:29
(Cont’d) … (7) Do you have the times stored as times somewhere? (8) How complicated a solution are you willing to accept? For example, if you need to do this only once (or perhaps once every year), then I would expect you to accept a solution that requires a few steps. If you need to do it once or more per month, I would expect you to demand something simple. Or do you want a solution that works automatically, continually — i.e., you can add data in an "Input" sheet and have the "Output" sheet immediately, automatically updated? … (Cont’d)
– Scott
Dec 16 at 0:29
(Cont’d) … Please do not respond in comments; edit your question to make it clearer and more complete.
– Scott
Dec 16 at 0:29
Scott, thank you for your comments/suggestions. I hope I have addressed all the issues and it is more easily understood now.
– gwmoto
Dec 16 at 23:53