Displaying Column names of empty cells for a given row
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
The basic premise is that I want to create a column that outputs the column names of the first 3-4 cells that are blank for its associated row. I have a picture of how it would work in theory. I can tell that the cells in my actual spreadsheet are truly empty since I can conditionally format them, but I have no scripting or formula skills to actually output information in a new column.
A solution for this would help out a lot of teachers and students keep better track of their progress so I really appreciate the help!
I tried using the new code there- it does display 3 column names for the first one, but when I copy that cell and select all the other cells in the column and paste it doesn't behave the same. I made a couple of different situations so hopefully that can help unravel it a bit. Should I be doing that in a different way?
Output Test with n first empty cells
microsoft-excel worksheet-function
asked Feb 6 at 20:34
pirateyoshipirateyoshi
32
add a comment |
The basic premise is that I want to create a column that outputs the column names of the first 3-4 cells that are blank for its associated row. I have a picture of how it would work in theory. I can tell that the cells in my actual spreadsheet are truly empty since I can conditionally format them, but I have no scripting or formula skills to actually output information in a new column.
A solution for this would help out a lot of teachers and students keep better track of their progress so I really appreciate the help!
I tried using the new code there- it does display 3 column names for the first one, but when I copy that cell and select all the other cells in the column and paste it doesn't behave the same. I made a couple of different situations so hopefully that can help unravel it a bit. Should I be doing that in a different way?
Output Test with n first empty cells
microsoft-excel worksheet-function
asked Feb 6 at 20:34
pirateyoshipirateyoshi
32
add a comment |
The basic premise is that I want to create a column that outputs the column names of the first 3-4 cells that are blank for its associated row. I have a picture of how it would work in theory. I can tell that the cells in my actual spreadsheet are truly empty since I can conditionally format them, but I have no scripting or formula skills to actually output information in a new column.
A solution for this would help out a lot of teachers and students keep better track of their progress so I really appreciate the help!
I tried using the new code there- it does display 3 column names for the first one, but when I copy that cell and select all the other cells in the column and paste it doesn't behave the same. I made a couple of different situations so hopefully that can help unravel it a bit. Should I be doing that in a different way?
Output Test with n first empty cells
microsoft-excel worksheet-function
asked Feb 6 at 20:34
pirateyoshipirateyoshi
32
The basic premise is that I want to create a column that outputs the column names of the first 3-4 cells that are blank for its associated row. I have a picture of how it would work in theory. I can tell that the cells in my actual spreadsheet are truly empty since I can conditionally format them, but I have no scripting or formula skills to actually output information in a new column.
A solution for this would help out a lot of teachers and students keep better track of their progress so I really appreciate the help!
I tried using the new code there- it does display 3 column names for the first one, but when I copy that cell and select all the other cells in the column and paste it doesn't behave the same. I made a couple of different situations so hopefully that can help unravel it a bit. Should I be doing that in a different way?
Output Test with n first empty cells
microsoft-excel worksheet-function
microsoft-excel worksheet-function
asked Feb 6 at 20:34
pirateyoshipirateyoshi
32
asked Feb 6 at 20:34
pirateyoshipirateyoshi
32
edited Feb 6 at 21:19
pirateyoshi
asked Feb 6 at 20:34
pirateyoshipirateyoshi
32
asked Feb 6 at 20:34
pirateyoshipirateyoshi
32
asked Feb 6 at 20:34
pirateyoshipirateyoshi
32
32
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
IF you have Office 365 Excel, use TEXTJOIN as an array formula"
=TEXTJOIN(", ",TRUE,IF(B2:I2="",$B$1:$I$1,""))
Being an array formula it must be entered with Ctrl-Shift-Enter instead of Enter when exiting edit mode.
to limit the number of returns it gets tricky:
=TEXTJOIN(", ",TRUE,IF(B2:INDEX(B2:I2,AGGREGATE(15,6,(COLUMN(B2:I2)-MIN(COLUMN(B2:I2))+1)/(B2:I2=""),MIN(3,SUMPRODUCT(--(B2:I2 = "")))))="",$B$1:INDEX($B$1:$I$1,AGGREGATE(15,6,(COLUMN(B2:I2)-MIN(COLUMN(B2:I2))+1)/(B2:I2=""),MIN(3,SUMPRODUCT(--(B2:I2 = ""))))),""))
Still an array formula. There are two places where I put 3 which indicates the max number of blanks to return.
answered Feb 6 at 20:47
Scott CranerScott Craner
12.6k11318
Wow thats awesome. And if it was a longer sheet I would just edit the I2 and $I$1 to whatever the last column is right? Now is there anyway for it not to output every single empty cell in the row? Perhaps the first n cells? This is amazing already but figured I would ask.
– pirateyoshi
Feb 6 at 20:53
@pirateyoshi yes.
– Scott Craner
Feb 6 at 20:53
Oh sorry I clicked enter too soon there I edited my post just now. :]
– pirateyoshi
Feb 6 at 20:54
@pirateyoshi see edit
– Scott Craner
Feb 6 at 21:03
Thanks, I edited my main post to show a couple of different scenarios that are happening with the other rows once I paste it. I cant really figure out why it only puts out 1 column name for some, and others 2, and some 3.
– pirateyoshi
Feb 6 at 21:20
|
show 1 more 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%2f1402847%2fdisplaying-column-names-of-empty-cells-for-a-given-row%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
IF you have Office 365 Excel, use TEXTJOIN as an array formula"
=TEXTJOIN(", ",TRUE,IF(B2:I2="",$B$1:$I$1,""))
Being an array formula it must be entered with Ctrl-Shift-Enter instead of Enter when exiting edit mode.
to limit the number of returns it gets tricky:
=TEXTJOIN(", ",TRUE,IF(B2:INDEX(B2:I2,AGGREGATE(15,6,(COLUMN(B2:I2)-MIN(COLUMN(B2:I2))+1)/(B2:I2=""),MIN(3,SUMPRODUCT(--(B2:I2 = "")))))="",$B$1:INDEX($B$1:$I$1,AGGREGATE(15,6,(COLUMN(B2:I2)-MIN(COLUMN(B2:I2))+1)/(B2:I2=""),MIN(3,SUMPRODUCT(--(B2:I2 = ""))))),""))
Still an array formula. There are two places where I put 3 which indicates the max number of blanks to return.
answered Feb 6 at 20:47
Scott CranerScott Craner
12.6k11318
Wow thats awesome. And if it was a longer sheet I would just edit the I2 and $I$1 to whatever the last column is right? Now is there anyway for it not to output every single empty cell in the row? Perhaps the first n cells? This is amazing already but figured I would ask.
– pirateyoshi
Feb 6 at 20:53
@pirateyoshi yes.
– Scott Craner
Feb 6 at 20:53
Oh sorry I clicked enter too soon there I edited my post just now. :]
– pirateyoshi
Feb 6 at 20:54
@pirateyoshi see edit
– Scott Craner
Feb 6 at 21:03
Thanks, I edited my main post to show a couple of different scenarios that are happening with the other rows once I paste it. I cant really figure out why it only puts out 1 column name for some, and others 2, and some 3.
– pirateyoshi
Feb 6 at 21:20
|
show 1 more comment
IF you have Office 365 Excel, use TEXTJOIN as an array formula"
=TEXTJOIN(", ",TRUE,IF(B2:I2="",$B$1:$I$1,""))
Being an array formula it must be entered with Ctrl-Shift-Enter instead of Enter when exiting edit mode.
to limit the number of returns it gets tricky:
=TEXTJOIN(", ",TRUE,IF(B2:INDEX(B2:I2,AGGREGATE(15,6,(COLUMN(B2:I2)-MIN(COLUMN(B2:I2))+1)/(B2:I2=""),MIN(3,SUMPRODUCT(--(B2:I2 = "")))))="",$B$1:INDEX($B$1:$I$1,AGGREGATE(15,6,(COLUMN(B2:I2)-MIN(COLUMN(B2:I2))+1)/(B2:I2=""),MIN(3,SUMPRODUCT(--(B2:I2 = ""))))),""))
Still an array formula. There are two places where I put 3 which indicates the max number of blanks to return.
answered Feb 6 at 20:47
Scott CranerScott Craner
12.6k11318
Wow thats awesome. And if it was a longer sheet I would just edit the I2 and $I$1 to whatever the last column is right? Now is there anyway for it not to output every single empty cell in the row? Perhaps the first n cells? This is amazing already but figured I would ask.
– pirateyoshi
Feb 6 at 20:53
@pirateyoshi yes.
– Scott Craner
Feb 6 at 20:53
Oh sorry I clicked enter too soon there I edited my post just now. :]
– pirateyoshi
Feb 6 at 20:54
@pirateyoshi see edit
– Scott Craner
Feb 6 at 21:03
Thanks, I edited my main post to show a couple of different scenarios that are happening with the other rows once I paste it. I cant really figure out why it only puts out 1 column name for some, and others 2, and some 3.
– pirateyoshi
Feb 6 at 21:20
|
show 1 more comment
IF you have Office 365 Excel, use TEXTJOIN as an array formula"
=TEXTJOIN(", ",TRUE,IF(B2:I2="",$B$1:$I$1,""))
Being an array formula it must be entered with Ctrl-Shift-Enter instead of Enter when exiting edit mode.
to limit the number of returns it gets tricky:
=TEXTJOIN(", ",TRUE,IF(B2:INDEX(B2:I2,AGGREGATE(15,6,(COLUMN(B2:I2)-MIN(COLUMN(B2:I2))+1)/(B2:I2=""),MIN(3,SUMPRODUCT(--(B2:I2 = "")))))="",$B$1:INDEX($B$1:$I$1,AGGREGATE(15,6,(COLUMN(B2:I2)-MIN(COLUMN(B2:I2))+1)/(B2:I2=""),MIN(3,SUMPRODUCT(--(B2:I2 = ""))))),""))
Still an array formula. There are two places where I put 3 which indicates the max number of blanks to return.
answered Feb 6 at 20:47
Scott CranerScott Craner
12.6k11318
IF you have Office 365 Excel, use TEXTJOIN as an array formula"
=TEXTJOIN(", ",TRUE,IF(B2:I2="",$B$1:$I$1,""))
Being an array formula it must be entered with Ctrl-Shift-Enter instead of Enter when exiting edit mode.
to limit the number of returns it gets tricky:
=TEXTJOIN(", ",TRUE,IF(B2:INDEX(B2:I2,AGGREGATE(15,6,(COLUMN(B2:I2)-MIN(COLUMN(B2:I2))+1)/(B2:I2=""),MIN(3,SUMPRODUCT(--(B2:I2 = "")))))="",$B$1:INDEX($B$1:$I$1,AGGREGATE(15,6,(COLUMN(B2:I2)-MIN(COLUMN(B2:I2))+1)/(B2:I2=""),MIN(3,SUMPRODUCT(--(B2:I2 = ""))))),""))
Still an array formula. There are two places where I put 3 which indicates the max number of blanks to return.
answered Feb 6 at 20:47
Scott CranerScott Craner
12.6k11318
edited Feb 6 at 21:21
answered Feb 6 at 20:47
Scott CranerScott Craner
12.6k11318
answered Feb 6 at 20:47
Scott CranerScott Craner
12.6k11318
answered Feb 6 at 20:47
Scott CranerScott Craner
12.6k11318
12.6k11318
Wow thats awesome. And if it was a longer sheet I would just edit the I2 and $I$1 to whatever the last column is right? Now is there anyway for it not to output every single empty cell in the row? Perhaps the first n cells? This is amazing already but figured I would ask.
– pirateyoshi
Feb 6 at 20:53
@pirateyoshi yes.
– Scott Craner
Feb 6 at 20:53
Oh sorry I clicked enter too soon there I edited my post just now. :]
– pirateyoshi
Feb 6 at 20:54
@pirateyoshi see edit
– Scott Craner
Feb 6 at 21:03
Thanks, I edited my main post to show a couple of different scenarios that are happening with the other rows once I paste it. I cant really figure out why it only puts out 1 column name for some, and others 2, and some 3.
– pirateyoshi
Feb 6 at 21:20
|
show 1 more comment
Wow thats awesome. And if it was a longer sheet I would just edit the I2 and $I$1 to whatever the last column is right? Now is there anyway for it not to output every single empty cell in the row? Perhaps the first n cells? This is amazing already but figured I would ask.
– pirateyoshi
Feb 6 at 20:53
@pirateyoshi yes.
– Scott Craner
Feb 6 at 20:53
Oh sorry I clicked enter too soon there I edited my post just now. :]
– pirateyoshi
Feb 6 at 20:54
@pirateyoshi see edit
– Scott Craner
Feb 6 at 21:03
Thanks, I edited my main post to show a couple of different scenarios that are happening with the other rows once I paste it. I cant really figure out why it only puts out 1 column name for some, and others 2, and some 3.
– pirateyoshi
Feb 6 at 21:20
Wow thats awesome. And if it was a longer sheet I would just edit the I2 and $I$1 to whatever the last column is right? Now is there anyway for it not to output every single empty cell in the row? Perhaps the first n cells? This is amazing already but figured I would ask.
– pirateyoshi
Feb 6 at 20:53
Wow thats awesome. And if it was a longer sheet I would just edit the I2 and $I$1 to whatever the last column is right? Now is there anyway for it not to output every single empty cell in the row? Perhaps the first n cells? This is amazing already but figured I would ask.
– pirateyoshi
Feb 6 at 20:53
@pirateyoshi yes.
– Scott Craner
Feb 6 at 20:53
@pirateyoshi yes.
– Scott Craner
Feb 6 at 20:53
Oh sorry I clicked enter too soon there I edited my post just now. :]
– pirateyoshi
Feb 6 at 20:54
Oh sorry I clicked enter too soon there I edited my post just now. :]
– pirateyoshi
Feb 6 at 20:54
@pirateyoshi see edit
– Scott Craner
Feb 6 at 21:03
@pirateyoshi see edit
– Scott Craner
Feb 6 at 21:03
Thanks, I edited my main post to show a couple of different scenarios that are happening with the other rows once I paste it. I cant really figure out why it only puts out 1 column name for some, and others 2, and some 3.
– pirateyoshi
Feb 6 at 21:20
Thanks, I edited my main post to show a couple of different scenarios that are happening with the other rows once I paste it. I cant really figure out why it only puts out 1 column name for some, and others 2, and some 3.
– pirateyoshi
Feb 6 at 21:20
|
show 1 more 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%2f1402847%2fdisplaying-column-names-of-empty-cells-for-a-given-row%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
