Get whole line containing last occurrence of value in google sheet
I have a long table (50k lines and 15 columns) where different users report a status using a google-form (the table is always chronologically sorted), and I want to obtain the last report of each user. I have managed to do it, but it takes too long to reload, I know that the formulas I use are too heavy for the spreadsheet, but I can't find any better way to do it.
To simplify the scenery, the case can be applied to the following input and desired output:
Input:
| Timestamp | User | Status |
|:-------------------:|:----:|:--------:|
| 2019-01-03 10:00:30 | A | Started |
| 2019-01-03 10:01:41 | B | Started |
| 2019-01-03 10:02:00 | B | Finished |
| 2019-01-03 10:02:14 | C | Started |
| 2019-01-03 10:03:22 | A | Failed |
| 2019-01-03 10:00:04 | C | Finished |
Output:
| Timestamp | User | Status |
|:-------------------:|:----:|:--------:|
| 2019-01-03 10:02:00 | B | Finished |
| 2019-01-03 10:03:22 | A | Failed |
| 2019-01-03 10:00:04 | C | Finished |
I managed to do it combining a pivot table with user as row and max(Timestamp) as value and then feed that information to a formula (maybe INDEX and MATCH would have been a better approach):
OFFSET('Sheet1'!$A$1,ArrayFormula(max(if('Sheet1'!$B:$B=$A3, Row('Sheet1'!$B:$B))))-1,column()-1)
I also tried with the following approach for each unique result.
| Unique users | Query |
|:----------------------:|:-----------------------------------------------------------------------------:|
| =UNIQUE('Sheet1'!$A:$A) | =QUERY('Sheet1'!$A:$C, "SELECT A, C WHERE B='"&B2&"' ORDER BY A DESC LIMIT 1",0) |
From what I have read, combining ARRAYFORMULA and QUERY might improve the performance, but I have not been able to pull it off.
google-sheets google-form google-sheets-query google-sheets-formula
asked Jan 3 at 14:41
5axola5axola
605
add a comment |
I have a long table (50k lines and 15 columns) where different users report a status using a google-form (the table is always chronologically sorted), and I want to obtain the last report of each user. I have managed to do it, but it takes too long to reload, I know that the formulas I use are too heavy for the spreadsheet, but I can't find any better way to do it.
To simplify the scenery, the case can be applied to the following input and desired output:
Input:
| Timestamp | User | Status |
|:-------------------:|:----:|:--------:|
| 2019-01-03 10:00:30 | A | Started |
| 2019-01-03 10:01:41 | B | Started |
| 2019-01-03 10:02:00 | B | Finished |
| 2019-01-03 10:02:14 | C | Started |
| 2019-01-03 10:03:22 | A | Failed |
| 2019-01-03 10:00:04 | C | Finished |
Output:
| Timestamp | User | Status |
|:-------------------:|:----:|:--------:|
| 2019-01-03 10:02:00 | B | Finished |
| 2019-01-03 10:03:22 | A | Failed |
| 2019-01-03 10:00:04 | C | Finished |
I managed to do it combining a pivot table with user as row and max(Timestamp) as value and then feed that information to a formula (maybe INDEX and MATCH would have been a better approach):
OFFSET('Sheet1'!$A$1,ArrayFormula(max(if('Sheet1'!$B:$B=$A3, Row('Sheet1'!$B:$B))))-1,column()-1)
I also tried with the following approach for each unique result.
| Unique users | Query |
|:----------------------:|:-----------------------------------------------------------------------------:|
| =UNIQUE('Sheet1'!$A:$A) | =QUERY('Sheet1'!$A:$C, "SELECT A, C WHERE B='"&B2&"' ORDER BY A DESC LIMIT 1",0) |
From what I have read, combining ARRAYFORMULA and QUERY might improve the performance, but I have not been able to pull it off.
google-sheets google-form google-sheets-query google-sheets-formula
asked Jan 3 at 14:41
5axola5axola
605
Array formulas will take too long with that many cells. If you are handy with google apps script you can write a script that does this for you once, and then another that executes on each new form submission if you want a 'live' version.
– e__n
Jan 4 at 0:26
This is the best approach when it comes to fluency as it does not use any formula, I am afraid that it's what I will have to do. For the moment I am going to use JPV's answer and I'll do an GAP when I have some spare time.
– 5axola
Jan 7 at 9:04
add a comment |
I have a long table (50k lines and 15 columns) where different users report a status using a google-form (the table is always chronologically sorted), and I want to obtain the last report of each user. I have managed to do it, but it takes too long to reload, I know that the formulas I use are too heavy for the spreadsheet, but I can't find any better way to do it.
To simplify the scenery, the case can be applied to the following input and desired output:
Input:
| Timestamp | User | Status |
|:-------------------:|:----:|:--------:|
| 2019-01-03 10:00:30 | A | Started |
| 2019-01-03 10:01:41 | B | Started |
| 2019-01-03 10:02:00 | B | Finished |
| 2019-01-03 10:02:14 | C | Started |
| 2019-01-03 10:03:22 | A | Failed |
| 2019-01-03 10:00:04 | C | Finished |
Output:
| Timestamp | User | Status |
|:-------------------:|:----:|:--------:|
| 2019-01-03 10:02:00 | B | Finished |
| 2019-01-03 10:03:22 | A | Failed |
| 2019-01-03 10:00:04 | C | Finished |
I managed to do it combining a pivot table with user as row and max(Timestamp) as value and then feed that information to a formula (maybe INDEX and MATCH would have been a better approach):
OFFSET('Sheet1'!$A$1,ArrayFormula(max(if('Sheet1'!$B:$B=$A3, Row('Sheet1'!$B:$B))))-1,column()-1)
I also tried with the following approach for each unique result.
| Unique users | Query |
|:----------------------:|:-----------------------------------------------------------------------------:|
| =UNIQUE('Sheet1'!$A:$A) | =QUERY('Sheet1'!$A:$C, "SELECT A, C WHERE B='"&B2&"' ORDER BY A DESC LIMIT 1",0) |
From what I have read, combining ARRAYFORMULA and QUERY might improve the performance, but I have not been able to pull it off.
google-sheets google-form google-sheets-query google-sheets-formula
asked Jan 3 at 14:41
5axola5axola
605
I have a long table (50k lines and 15 columns) where different users report a status using a google-form (the table is always chronologically sorted), and I want to obtain the last report of each user. I have managed to do it, but it takes too long to reload, I know that the formulas I use are too heavy for the spreadsheet, but I can't find any better way to do it.
To simplify the scenery, the case can be applied to the following input and desired output:
Input:
| Timestamp | User | Status |
|:-------------------:|:----:|:--------:|
| 2019-01-03 10:00:30 | A | Started |
| 2019-01-03 10:01:41 | B | Started |
| 2019-01-03 10:02:00 | B | Finished |
| 2019-01-03 10:02:14 | C | Started |
| 2019-01-03 10:03:22 | A | Failed |
| 2019-01-03 10:00:04 | C | Finished |
Output:
| Timestamp | User | Status |
|:-------------------:|:----:|:--------:|
| 2019-01-03 10:02:00 | B | Finished |
| 2019-01-03 10:03:22 | A | Failed |
| 2019-01-03 10:00:04 | C | Finished |
I managed to do it combining a pivot table with user as row and max(Timestamp) as value and then feed that information to a formula (maybe INDEX and MATCH would have been a better approach):
OFFSET('Sheet1'!$A$1,ArrayFormula(max(if('Sheet1'!$B:$B=$A3, Row('Sheet1'!$B:$B))))-1,column()-1)
I also tried with the following approach for each unique result.
| Unique users | Query |
|:----------------------:|:-----------------------------------------------------------------------------:|
| =UNIQUE('Sheet1'!$A:$A) | =QUERY('Sheet1'!$A:$C, "SELECT A, C WHERE B='"&B2&"' ORDER BY A DESC LIMIT 1",0) |
From what I have read, combining ARRAYFORMULA and QUERY might improve the performance, but I have not been able to pull it off.
google-sheets google-form google-sheets-query google-sheets-formula
google-sheets google-form google-sheets-query google-sheets-formula
asked Jan 3 at 14:41
5axola5axola
605
asked Jan 3 at 14:41
5axola5axola
605
asked Jan 3 at 14:41
5axola5axola
605
asked Jan 3 at 14:41
5axola5axola
605
asked Jan 3 at 14:41
5axola5axola
605
605
Array formulas will take too long with that many cells. If you are handy with google apps script you can write a script that does this for you once, and then another that executes on each new form submission if you want a 'live' version.
– e__n
Jan 4 at 0:26
This is the best approach when it comes to fluency as it does not use any formula, I am afraid that it's what I will have to do. For the moment I am going to use JPV's answer and I'll do an GAP when I have some spare time.
– 5axola
Jan 7 at 9:04
add a comment |
Array formulas will take too long with that many cells. If you are handy with google apps script you can write a script that does this for you once, and then another that executes on each new form submission if you want a 'live' version.
– e__n
Jan 4 at 0:26
This is the best approach when it comes to fluency as it does not use any formula, I am afraid that it's what I will have to do. For the moment I am going to use JPV's answer and I'll do an GAP when I have some spare time.
– 5axola
Jan 7 at 9:04
Array formulas will take too long with that many cells. If you are handy with google apps script you can write a script that does this for you once, and then another that executes on each new form submission if you want a 'live' version.
– e__n
Jan 4 at 0:26
Array formulas will take too long with that many cells. If you are handy with google apps script you can write a script that does this for you once, and then another that executes on each new form submission if you want a 'live' version.
– e__n
Jan 4 at 0:26
This is the best approach when it comes to fluency as it does not use any formula, I am afraid that it's what I will have to do. For the moment I am going to use JPV's answer and I'll do an GAP when I have some spare time.
– 5axola
Jan 7 at 9:04
This is the best approach when it comes to fluency as it does not use any formula, I am afraid that it's what I will have to do. For the moment I am going to use JPV's answer and I'll do an GAP when I have some spare time.
– 5axola
Jan 7 at 9:04
add a comment |
1 Answer
1
active
oldest
votes
Assuming Timestamp in col A, User in Col B and Status in col C try
=Arrayformula(iferror(vlookup(unique (B2:B), sort({B2:B, A2:C}, 2, 0), {2, 3, 4}, 0)))
answered Jan 3 at 15:12
JPVJPV
11.4k21627
Thank you. It stills takes its toll on the sheet, but it's definitively more fluid.
– 5axola
Jan 7 at 9:01
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%2f54024478%2fget-whole-line-containing-last-occurrence-of-value-in-google-sheet%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 Timestamp in col A, User in Col B and Status in col C try
=Arrayformula(iferror(vlookup(unique (B2:B), sort({B2:B, A2:C}, 2, 0), {2, 3, 4}, 0)))
answered Jan 3 at 15:12
JPVJPV
11.4k21627
Thank you. It stills takes its toll on the sheet, but it's definitively more fluid.
– 5axola
Jan 7 at 9:01
add a comment |
Assuming Timestamp in col A, User in Col B and Status in col C try
=Arrayformula(iferror(vlookup(unique (B2:B), sort({B2:B, A2:C}, 2, 0), {2, 3, 4}, 0)))
answered Jan 3 at 15:12
JPVJPV
11.4k21627
Thank you. It stills takes its toll on the sheet, but it's definitively more fluid.
– 5axola
Jan 7 at 9:01
add a comment |
Assuming Timestamp in col A, User in Col B and Status in col C try
=Arrayformula(iferror(vlookup(unique (B2:B), sort({B2:B, A2:C}, 2, 0), {2, 3, 4}, 0)))
answered Jan 3 at 15:12
JPVJPV
11.4k21627
Assuming Timestamp in col A, User in Col B and Status in col C try
=Arrayformula(iferror(vlookup(unique (B2:B), sort({B2:B, A2:C}, 2, 0), {2, 3, 4}, 0)))
answered Jan 3 at 15:12
JPVJPV
11.4k21627
answered Jan 3 at 15:12
JPVJPV
11.4k21627
answered Jan 3 at 15:12
JPVJPV
11.4k21627
answered Jan 3 at 15:12
JPVJPV
11.4k21627
11.4k21627
Thank you. It stills takes its toll on the sheet, but it's definitively more fluid.
– 5axola
Jan 7 at 9:01
add a comment |
Thank you. It stills takes its toll on the sheet, but it's definitively more fluid.
– 5axola
Jan 7 at 9:01
Thank you. It stills takes its toll on the sheet, but it's definitively more fluid.
– 5axola
Jan 7 at 9:01
Thank you. It stills takes its toll on the sheet, but it's definitively more fluid.
– 5axola
Jan 7 at 9:01
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%2f54024478%2fget-whole-line-containing-last-occurrence-of-value-in-google-sheet%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
Array formulas will take too long with that many cells. If you are handy with google apps script you can write a script that does this for you once, and then another that executes on each new form submission if you want a 'live' version.
– e__n
Jan 4 at 0:26
This is the best approach when it comes to fluency as it does not use any formula, I am afraid that it's what I will have to do. For the moment I am going to use JPV's answer and I'll do an GAP when I have some spare time.
– 5axola
Jan 7 at 9:04