Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help combining multiple columns into one
Hi!
Thanks in advance for any advice. I am a relative novice stuck at a crossroads and really appreciate any help. I've created a spreadsheet that spits out the information I need organized in the following format: .. A - B - C - D - E - F - G - H - I - J - K - L ..... 1 X X X X 2 X X X X 3 X X X X 4 X X X X 5 X X X X 6 X X X X ____________________________ This means that, on any given row, there are 4 (and only 4) columns containing information - and the rest of the cells are blank. Now I need to know how to consolidate this information so that it is all contained in one set of columns (eg): .. A - B - C - D 1 X X X X 2 X X X X 3 X X X X 4 X X X X 5 X X X X 6 X X X X _____________________________ Like I said, I have it set up so that, on every single row, there are exactly 4 consecutive cells containing info. I have also made sure that there will be no blank rows to deal with (every row WILL contain 4 cells of info). However, the placement of those 4 cells along the X axis varies from row to row (sometimes it is in cells A-D, and sometimes in cells CH-CK, etc). In simple terms, I need to just end up with a sheet containing only 4 columns of data, no more. I never realized how hard it was to explain a spreadsheet! Hopefully I made some sense, and someone out there will have some advice. Let me know if there are any details I need to add that I've forgotten. Thx again! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help combining multiple columns into one
Thanks for the tip, but unfortunately it did not work. Excel
highlights only entire columns that have no data, but if there is data in even just one cell in a column, it will not highlight the blank spaces in that column to be deleted. What I need is a way to delete the blank cells in the first X amount of cells that precede the cells containing actual data. This 'X' number will change from row to row (so in row 1, there could be only 4 blank spaces to be deleted, but in row 2 there may be 20 blank spaces, and in row 3 there may be 12, etc...). Eventually, no matter how many blanks needed to be removed from each row, I want to end up with a sheet listing data only in columns A-D. The tip you provided only allows me to select large, contiguous areas of uninterrupted blank cells when I really need to be able to select a bizarre kind of mix-n-match. I fear I am just making things more confusing the more I try to explain myself. Does what I'm looking for make sense? Is it just not possible? Thx. Dave Peterson wrote: Select all the columns that have something in them (or select the whole worksheet if you want). Edit|goto|special|check blanks edit|delete|shift cells left You may want to do it against a test worksheet--just in case that's not what you really want. wrote: Hi! Thanks in advance for any advice. I am a relative novice stuck at a crossroads and really appreciate any help. I've created a spreadsheet that spits out the information I need organized in the following format: . A - B - C - D - E - F - G - H - I - J - K - L ..... 1 X X X X 2 X X X X 3 X X X X 4 X X X X 5 X X X X 6 X X X X ____________________________ This means that, on any given row, there are 4 (and only 4) columns containing information - and the rest of the cells are blank. Now I need to know how to consolidate this information so that it is all contained in one set of columns (eg): . A - B - C - D 1 X X X X 2 X X X X 3 X X X X 4 X X X X 5 X X X X 6 X X X X _____________________________ Like I said, I have it set up so that, on every single row, there are exactly 4 consecutive cells containing info. I have also made sure that there will be no blank rows to deal with (every row WILL contain 4 cells of info). However, the placement of those 4 cells along the X axis varies from row to row (sometimes it is in cells A-D, and sometimes in cells CH-CK, etc). In simple terms, I need to just end up with a sheet containing only 4 columns of data, no more. I never realized how hard it was to explain a spreadsheet! Hopefully I made some sense, and someone out there will have some advice. Let me know if there are any details I need to add that I've forgotten. Thx again! -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help combining multiple columns into one
Hi,
I did exactly what Dave said but starting with simple data, and it works. I even a row that starts with data from A and end at D, then the next row starts at Z and end at AC, and even some rows that are empty and it works perfectly. You may want to try again, maybe with some simple data first to make sure you do it correctly. Regards, Dolphin " wrote: Thanks for the tip, but unfortunately it did not work. Excel highlights only entire columns that have no data, but if there is data in even just one cell in a column, it will not highlight the blank spaces in that column to be deleted. What I need is a way to delete the blank cells in the first X amount of cells that precede the cells containing actual data. This 'X' number will change from row to row (so in row 1, there could be only 4 blank spaces to be deleted, but in row 2 there may be 20 blank spaces, and in row 3 there may be 12, etc...). Eventually, no matter how many blanks needed to be removed from each row, I want to end up with a sheet listing data only in columns A-D. The tip you provided only allows me to select large, contiguous areas of uninterrupted blank cells when I really need to be able to select a bizarre kind of mix-n-match. I fear I am just making things more confusing the more I try to explain myself. Does what I'm looking for make sense? Is it just not possible? Thx. Dave Peterson wrote: Select all the columns that have something in them (or select the whole worksheet if you want). Edit|goto|special|check blanks edit|delete|shift cells left You may want to do it against a test worksheet--just in case that's not what you really want. wrote: Hi! Thanks in advance for any advice. I am a relative novice stuck at a crossroads and really appreciate any help. I've created a spreadsheet that spits out the information I need organized in the following format: . A - B - C - D - E - F - G - H - I - J - K - L ..... 1 X X X X 2 X X X X 3 X X X X 4 X X X X 5 X X X X 6 X X X X ____________________________ This means that, on any given row, there are 4 (and only 4) columns containing information - and the rest of the cells are blank. Now I need to know how to consolidate this information so that it is all contained in one set of columns (eg): . A - B - C - D 1 X X X X 2 X X X X 3 X X X X 4 X X X X 5 X X X X 6 X X X X _____________________________ Like I said, I have it set up so that, on every single row, there are exactly 4 consecutive cells containing info. I have also made sure that there will be no blank rows to deal with (every row WILL contain 4 cells of info). However, the placement of those 4 cells along the X axis varies from row to row (sometimes it is in cells A-D, and sometimes in cells CH-CK, etc). In simple terms, I need to just end up with a sheet containing only 4 columns of data, no more. I never realized how hard it was to explain a spreadsheet! Hopefully I made some sense, and someone out there will have some advice. Let me know if there are any details I need to add that I've forgotten. Thx again! -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help combining multiple columns into one
It did work for me.
I'd try it once more. And it worked for Dolphin (if that is his/her real name <bg). wrote: Thanks for the tip, but unfortunately it did not work. Excel highlights only entire columns that have no data, but if there is data in even just one cell in a column, it will not highlight the blank spaces in that column to be deleted. What I need is a way to delete the blank cells in the first X amount of cells that precede the cells containing actual data. This 'X' number will change from row to row (so in row 1, there could be only 4 blank spaces to be deleted, but in row 2 there may be 20 blank spaces, and in row 3 there may be 12, etc...). Eventually, no matter how many blanks needed to be removed from each row, I want to end up with a sheet listing data only in columns A-D. The tip you provided only allows me to select large, contiguous areas of uninterrupted blank cells when I really need to be able to select a bizarre kind of mix-n-match. I fear I am just making things more confusing the more I try to explain myself. Does what I'm looking for make sense? Is it just not possible? Thx. Dave Peterson wrote: Select all the columns that have something in them (or select the whole worksheet if you want). Edit|goto|special|check blanks edit|delete|shift cells left You may want to do it against a test worksheet--just in case that's not what you really want. wrote: Hi! Thanks in advance for any advice. I am a relative novice stuck at a crossroads and really appreciate any help. I've created a spreadsheet that spits out the information I need organized in the following format: . A - B - C - D - E - F - G - H - I - J - K - L ..... 1 X X X X 2 X X X X 3 X X X X 4 X X X X 5 X X X X 6 X X X X ____________________________ This means that, on any given row, there are 4 (and only 4) columns containing information - and the rest of the cells are blank. Now I need to know how to consolidate this information so that it is all contained in one set of columns (eg): . A - B - C - D 1 X X X X 2 X X X X 3 X X X X 4 X X X X 5 X X X X 6 X X X X _____________________________ Like I said, I have it set up so that, on every single row, there are exactly 4 consecutive cells containing info. I have also made sure that there will be no blank rows to deal with (every row WILL contain 4 cells of info). However, the placement of those 4 cells along the X axis varies from row to row (sometimes it is in cells A-D, and sometimes in cells CH-CK, etc). In simple terms, I need to just end up with a sheet containing only 4 columns of data, no more. I never realized how hard it was to explain a spreadsheet! Hopefully I made some sense, and someone out there will have some advice. Let me know if there are any details I need to add that I've forgotten. Thx again! -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help combining multiple columns into one
Thanks guys, I figured out what the problem is (though I could still
use some help fixing it!) 1) The source sheet (the one that contains all the data spread out over different columns that I need to consolidate) is created by a series of Vlookups. 2) What I am doing - once the formulas have retrieved all the correct info - is highlighting/copying that page and doing a paste/special/values onto a new sheet (so that the cells on this new sheet contain the actual data, not the vlookup formula that retrieved it). 3) On this NEW worksheet, I am trying to use the 'select only blanks' tip that was kindly offered above. 4) The problem is this: when I paste/special/values onto a new worksheet... even though the cells LOOK blank... excel does not recognize those blank cells as truly empty unless I physically go over each one and manually press delete. I assume this is because the paste/special/values function is putting some kind of invisible placeholder in those cells for which my Vlookup returned a 'false'. I imagine this will be fixable if I can change my Vlookup function to return TRUE BLANKS when the logic test comes back false (I thought it WAS returning blanks, but I guess it is populating the cell with a 'space'). Here is a sample of the Vlookup formula I am using. Can someone tell me how to modify it so that it returns a TRUE BLANK in a 'false' cell - instead of a placeholder space that cannot be autodeleted using the tip provided above?: =IF(ISNA(VLOOKUP("COMPLETED",'Organize by Final Delivery Date'!A2:T2,20,FALSE)),"",VLOOKUP("COMPLETED",'Org anize by Final Delivery Date'!A2:T2,20,FALSE)) THANKS AGAIN TO ALL! YOU ARE SAVING MY LIFE!!!! :) Dave Peterson wrote: It did work for me. I'd try it once more. And it worked for Dolphin (if that is his/her real name <bg). wrote: Thanks for the tip, but unfortunately it did not work. Excel highlights only entire columns that have no data, but if there is data in even just one cell in a column, it will not highlight the blank spaces in that column to be deleted. What I need is a way to delete the blank cells in the first X amount of cells that precede the cells containing actual data. This 'X' number will change from row to row (so in row 1, there could be only 4 blank spaces to be deleted, but in row 2 there may be 20 blank spaces, and in row 3 there may be 12, etc...). Eventually, no matter how many blanks needed to be removed from each row, I want to end up with a sheet listing data only in columns A-D. The tip you provided only allows me to select large, contiguous areas of uninterrupted blank cells when I really need to be able to select a bizarre kind of mix-n-match. I fear I am just making things more confusing the more I try to explain myself. Does what I'm looking for make sense? Is it just not possible? Thx. Dave Peterson wrote: Select all the columns that have something in them (or select the whole worksheet if you want). Edit|goto|special|check blanks edit|delete|shift cells left You may want to do it against a test worksheet--just in case that's not what you really want. wrote: Hi! Thanks in advance for any advice. I am a relative novice stuck at a crossroads and really appreciate any help. I've created a spreadsheet that spits out the information I need organized in the following format: . A - B - C - D - E - F - G - H - I - J - K - L ..... 1 X X X X 2 X X X X 3 X X X X 4 X X X X 5 X X X X 6 X X X X ____________________________ This means that, on any given row, there are 4 (and only 4) columns containing information - and the rest of the cells are blank. Now I need to know how to consolidate this information so that it is all contained in one set of columns (eg): . A - B - C - D 1 X X X X 2 X X X X 3 X X X X 4 X X X X 5 X X X X 6 X X X X _____________________________ Like I said, I have it set up so that, on every single row, there are exactly 4 consecutive cells containing info. I have also made sure that there will be no blank rows to deal with (every row WILL contain 4 cells of info). However, the placement of those 4 cells along the X axis varies from row to row (sometimes it is in cells A-D, and sometimes in cells CH-CK, etc). In simple terms, I need to just end up with a sheet containing only 4 columns of data, no more. I never realized how hard it was to explain a spreadsheet! Hopefully I made some sense, and someone out there will have some advice. Let me know if there are any details I need to add that I've forgotten. Thx again! -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help combining multiple columns into one
=IF(ISNA(VLOOKUP("COMPLETED",'Organize by Final Delivery Date'!A2:T2,20,FALSE)),"///",VLOOKUP("COMPLETED",'Organize by Final Delivery Date'!A2:T2,20,FALSE)) Okay, there's my workaround. After searching the group (should've probably done that first :P), I've discovered that the 'forumlas won't return true blanks' problem is a common complaint about excel. So I changed my Vlookup to return "///" if it's false, and then I'll do a search/replace all and change every instance of "///" to a true blank that way (after pasting/special/values to a new sheet of course). It's an extra step in what I'd hoped would be a fully automated process, but doing that little bit extra allows me to select all blanks and delete them so the columns end up organized how I need them. I'll take it. Thanks again for the advice! wrote: Thanks guys, I figured out what the problem is (though I could still use some help fixing it!) 1) The source sheet (the one that contains all the data spread out over different columns that I need to consolidate) is created by a series of Vlookups. 2) What I am doing - once the formulas have retrieved all the correct info - is highlighting/copying that page and doing a paste/special/values onto a new sheet (so that the cells on this new sheet contain the actual data, not the vlookup formula that retrieved it). 3) On this NEW worksheet, I am trying to use the 'select only blanks' tip that was kindly offered above. 4) The problem is this: when I paste/special/values onto a new worksheet... even though the cells LOOK blank... excel does not recognize those blank cells as truly empty unless I physically go over each one and manually press delete. I assume this is because the paste/special/values function is putting some kind of invisible placeholder in those cells for which my Vlookup returned a 'false'. I imagine this will be fixable if I can change my Vlookup function to return TRUE BLANKS when the logic test comes back false (I thought it WAS returning blanks, but I guess it is populating the cell with a 'space'). Here is a sample of the Vlookup formula I am using. Can someone tell me how to modify it so that it returns a TRUE BLANK in a 'false' cell - instead of a placeholder space that cannot be autodeleted using the tip provided above?: =IF(ISNA(VLOOKUP("COMPLETED",'Organize by Final Delivery Date'!A2:T2,20,FALSE)),"",VLOOKUP("COMPLETED",'Org anize by Final Delivery Date'!A2:T2,20,FALSE)) THANKS AGAIN TO ALL! YOU ARE SAVING MY LIFE!!!! :) Dave Peterson wrote: It did work for me. I'd try it once more. And it worked for Dolphin (if that is his/her real name <bg). wrote: Thanks for the tip, but unfortunately it did not work. Excel highlights only entire columns that have no data, but if there is data in even just one cell in a column, it will not highlight the blank spaces in that column to be deleted. What I need is a way to delete the blank cells in the first X amount of cells that precede the cells containing actual data. This 'X' number will change from row to row (so in row 1, there could be only 4 blank spaces to be deleted, but in row 2 there may be 20 blank spaces, and in row 3 there may be 12, etc...). Eventually, no matter how many blanks needed to be removed from each row, I want to end up with a sheet listing data only in columns A-D. The tip you provided only allows me to select large, contiguous areas of uninterrupted blank cells when I really need to be able to select a bizarre kind of mix-n-match. I fear I am just making things more confusing the more I try to explain myself. Does what I'm looking for make sense? Is it just not possible? Thx. Dave Peterson wrote: Select all the columns that have something in them (or select the whole worksheet if you want). Edit|goto|special|check blanks edit|delete|shift cells left You may want to do it against a test worksheet--just in case that's not what you really want. wrote: Hi! Thanks in advance for any advice. I am a relative novice stuck at a crossroads and really appreciate any help. I've created a spreadsheet that spits out the information I need organized in the following format: . A - B - C - D - E - F - G - H - I - J - K - L ..... 1 X X X X 2 X X X X 3 X X X X 4 X X X X 5 X X X X 6 X X X X ____________________________ This means that, on any given row, there are 4 (and only 4) columns containing information - and the rest of the cells are blank. Now I need to know how to consolidate this information so that it is all contained in one set of columns (eg): . A - B - C - D 1 X X X X 2 X X X X 3 X X X X 4 X X X X 5 X X X X 6 X X X X _____________________________ Like I said, I have it set up so that, on every single row, there are exactly 4 consecutive cells containing info. I have also made sure that there will be no blank rows to deal with (every row WILL contain 4 cells of info). However, the placement of those 4 cells along the X axis varies from row to row (sometimes it is in cells A-D, and sometimes in cells CH-CK, etc). In simple terms, I need to just end up with a sheet containing only 4 columns of data, no more. I never realized how hard it was to explain a spreadsheet! Hopefully I made some sense, and someone out there will have some advice. Let me know if there are any details I need to add that I've forgotten. Thx again! -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help combining multiple columns into one
There are a couple of other workarounds you could use, too:
If I return "" to a cell and later convert to values, I'll select that range then Edit|replace what: (leave blank) with: $$$$$ replace all And then do the reverse: (with the range still selected) edit|Replace what: $$$$$ with: (leave blank) replace all ======= But in your case, I'd just use a formula like: =VLOOKUP("COMPLETED",'Organize by Final Delivery Date'!A2:T2,20,FALSE) And let the #n/a's be #n/a's. Convert to values Then Edit|replace what: #n/a with: (leave blank) replace all It might make your formulas re-evaluate a bit quicker (since it's only doing one =vlookup()) and who cares if you're converting to values and then cleaning up. wrote: =IF(ISNA(VLOOKUP("COMPLETED",'Organize by Final Delivery Date'!A2:T2,20,FALSE)),"///",VLOOKUP("COMPLETED",'Organize by Final Delivery Date'!A2:T2,20,FALSE)) Okay, there's my workaround. After searching the group (should've probably done that first :P), I've discovered that the 'forumlas won't return true blanks' problem is a common complaint about excel. So I changed my Vlookup to return "///" if it's false, and then I'll do a search/replace all and change every instance of "///" to a true blank that way (after pasting/special/values to a new sheet of course). It's an extra step in what I'd hoped would be a fully automated process, but doing that little bit extra allows me to select all blanks and delete them so the columns end up organized how I need them. I'll take it. Thanks again for the advice! wrote: Thanks guys, I figured out what the problem is (though I could still use some help fixing it!) 1) The source sheet (the one that contains all the data spread out over different columns that I need to consolidate) is created by a series of Vlookups. 2) What I am doing - once the formulas have retrieved all the correct info - is highlighting/copying that page and doing a paste/special/values onto a new sheet (so that the cells on this new sheet contain the actual data, not the vlookup formula that retrieved it). 3) On this NEW worksheet, I am trying to use the 'select only blanks' tip that was kindly offered above. 4) The problem is this: when I paste/special/values onto a new worksheet... even though the cells LOOK blank... excel does not recognize those blank cells as truly empty unless I physically go over each one and manually press delete. I assume this is because the paste/special/values function is putting some kind of invisible placeholder in those cells for which my Vlookup returned a 'false'. I imagine this will be fixable if I can change my Vlookup function to return TRUE BLANKS when the logic test comes back false (I thought it WAS returning blanks, but I guess it is populating the cell with a 'space'). Here is a sample of the Vlookup formula I am using. Can someone tell me how to modify it so that it returns a TRUE BLANK in a 'false' cell - instead of a placeholder space that cannot be autodeleted using the tip provided above?: =IF(ISNA(VLOOKUP("COMPLETED",'Organize by Final Delivery Date'!A2:T2,20,FALSE)),"",VLOOKUP("COMPLETED",'Org anize by Final Delivery Date'!A2:T2,20,FALSE)) THANKS AGAIN TO ALL! YOU ARE SAVING MY LIFE!!!! :) Dave Peterson wrote: It did work for me. I'd try it once more. And it worked for Dolphin (if that is his/her real name <bg). wrote: Thanks for the tip, but unfortunately it did not work. Excel highlights only entire columns that have no data, but if there is data in even just one cell in a column, it will not highlight the blank spaces in that column to be deleted. What I need is a way to delete the blank cells in the first X amount of cells that precede the cells containing actual data. This 'X' number will change from row to row (so in row 1, there could be only 4 blank spaces to be deleted, but in row 2 there may be 20 blank spaces, and in row 3 there may be 12, etc...). Eventually, no matter how many blanks needed to be removed from each row, I want to end up with a sheet listing data only in columns A-D. The tip you provided only allows me to select large, contiguous areas of uninterrupted blank cells when I really need to be able to select a bizarre kind of mix-n-match. I fear I am just making things more confusing the more I try to explain myself. Does what I'm looking for make sense? Is it just not possible? Thx. Dave Peterson wrote: Select all the columns that have something in them (or select the whole worksheet if you want). Edit|goto|special|check blanks edit|delete|shift cells left You may want to do it against a test worksheet--just in case that's not what you really want. wrote: Hi! Thanks in advance for any advice. I am a relative novice stuck at a crossroads and really appreciate any help. I've created a spreadsheet that spits out the information I need organized in the following format: . A - B - C - D - E - F - G - H - I - J - K - L ..... 1 X X X X 2 X X X X 3 X X X X 4 X X X X 5 X X X X 6 X X X X ____________________________ This means that, on any given row, there are 4 (and only 4) columns containing information - and the rest of the cells are blank. Now I need to know how to consolidate this information so that it is all contained in one set of columns (eg): . A - B - C - D 1 X X X X 2 X X X X 3 X X X X 4 X X X X 5 X X X X 6 X X X X _____________________________ Like I said, I have it set up so that, on every single row, there are exactly 4 consecutive cells containing info. I have also made sure that there will be no blank rows to deal with (every row WILL contain 4 cells of info). However, the placement of those 4 cells along the X axis varies from row to row (sometimes it is in cells A-D, and sometimes in cells CH-CK, etc). In simple terms, I need to just end up with a sheet containing only 4 columns of data, no more. I never realized how hard it was to explain a spreadsheet! Hopefully I made some sense, and someone out there will have some advice. Let me know if there are any details I need to add that I've forgotten. Thx again! -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help combining multiple columns into one
That's true. I will use this shorter version of the Vlookup formula -
because as long as I'm going to be doing a search & replace anyway, I don't need anything special to come back if it's 'false'. #N/A is just as easy to work with as ///. This has been a big help. Much appreciated. Dave Peterson wrote: There are a couple of other workarounds you could use, too: If I return "" to a cell and later convert to values, I'll select that range then Edit|replace what: (leave blank) with: $$$$$ replace all And then do the reverse: (with the range still selected) edit|Replace what: $$$$$ with: (leave blank) replace all ======= But in your case, I'd just use a formula like: =VLOOKUP("COMPLETED",'Organize by Final Delivery Date'!A2:T2,20,FALSE) And let the #n/a's be #n/a's. Convert to values Then Edit|replace what: #n/a with: (leave blank) replace all It might make your formulas re-evaluate a bit quicker (since it's only doing one =vlookup()) and who cares if you're converting to values and then cleaning up. wrote: =IF(ISNA(VLOOKUP("COMPLETED",'Organize by Final Delivery Date'!A2:T2,20,FALSE)),"///",VLOOKUP("COMPLETED",'Organize by Final Delivery Date'!A2:T2,20,FALSE)) Okay, there's my workaround. After searching the group (should've probably done that first :P), I've discovered that the 'forumlas won't return true blanks' problem is a common complaint about excel. So I changed my Vlookup to return "///" if it's false, and then I'll do a search/replace all and change every instance of "///" to a true blank that way (after pasting/special/values to a new sheet of course). It's an extra step in what I'd hoped would be a fully automated process, but doing that little bit extra allows me to select all blanks and delete them so the columns end up organized how I need them. I'll take it. Thanks again for the advice! wrote: Thanks guys, I figured out what the problem is (though I could still use some help fixing it!) 1) The source sheet (the one that contains all the data spread out over different columns that I need to consolidate) is created by a series of Vlookups. 2) What I am doing - once the formulas have retrieved all the correct info - is highlighting/copying that page and doing a paste/special/values onto a new sheet (so that the cells on this new sheet contain the actual data, not the vlookup formula that retrieved it). 3) On this NEW worksheet, I am trying to use the 'select only blanks' tip that was kindly offered above. 4) The problem is this: when I paste/special/values onto a new worksheet... even though the cells LOOK blank... excel does not recognize those blank cells as truly empty unless I physically go over each one and manually press delete. I assume this is because the paste/special/values function is putting some kind of invisible placeholder in those cells for which my Vlookup returned a 'false'. I imagine this will be fixable if I can change my Vlookup function to return TRUE BLANKS when the logic test comes back false (I thought it WAS returning blanks, but I guess it is populating the cell with a 'space'). Here is a sample of the Vlookup formula I am using. Can someone tell me how to modify it so that it returns a TRUE BLANK in a 'false' cell - instead of a placeholder space that cannot be autodeleted using the tip provided above?: =IF(ISNA(VLOOKUP("COMPLETED",'Organize by Final Delivery Date'!A2:T2,20,FALSE)),"",VLOOKUP("COMPLETED",'Org anize by Final Delivery Date'!A2:T2,20,FALSE)) THANKS AGAIN TO ALL! YOU ARE SAVING MY LIFE!!!! :) Dave Peterson wrote: It did work for me. I'd try it once more. And it worked for Dolphin (if that is his/her real name <bg). wrote: Thanks for the tip, but unfortunately it did not work. Excel highlights only entire columns that have no data, but if there is data in even just one cell in a column, it will not highlight the blank spaces in that column to be deleted. What I need is a way to delete the blank cells in the first X amount of cells that precede the cells containing actual data. This 'X' number will change from row to row (so in row 1, there could be only 4 blank spaces to be deleted, but in row 2 there may be 20 blank spaces, and in row 3 there may be 12, etc...). Eventually, no matter how many blanks needed to be removed from each row, I want to end up with a sheet listing data only in columns A-D. The tip you provided only allows me to select large, contiguous areas of uninterrupted blank cells when I really need to be able to select a bizarre kind of mix-n-match. I fear I am just making things more confusing the more I try to explain myself. Does what I'm looking for make sense? Is it just not possible? Thx. Dave Peterson wrote: Select all the columns that have something in them (or select the whole worksheet if you want). Edit|goto|special|check blanks edit|delete|shift cells left You may want to do it against a test worksheet--just in case that's not what you really want. wrote: Hi! Thanks in advance for any advice. I am a relative novice stuck at a crossroads and really appreciate any help. I've created a spreadsheet that spits out the information I need organized in the following format: . A - B - C - D - E - F - G - H - I - J - K - L ..... 1 X X X X 2 X X X X 3 X X X X 4 X X X X 5 X X X X 6 X X X X ____________________________ This means that, on any given row, there are 4 (and only 4) columns containing information - and the rest of the cells are blank. Now I need to know how to consolidate this information so that it is all contained in one set of columns (eg): . A - B - C - D 1 X X X X 2 X X X X 3 X X X X 4 X X X X 5 X X X X 6 X X X X _____________________________ Like I said, I have it set up so that, on every single row, there are exactly 4 consecutive cells containing info. I have also made sure that there will be no blank rows to deal with (every row WILL contain 4 cells of info). However, the placement of those 4 cells along the X axis varies from row to row (sometimes it is in cells A-D, and sometimes in cells CH-CK, etc). In simple terms, I need to just end up with a sheet containing only 4 columns of data, no more. I never realized how hard it was to explain a spreadsheet! Hopefully I made some sense, and someone out there will have some advice. Let me know if there are any details I need to add that I've forgotten. Thx again! -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combine multiple columns into two long columns, Repeating rows in first column | Excel Discussion (Misc queries) | |||
Search Multiple columns for criterion asterisk (*) and Return Numeric Label | Excel Worksheet Functions | |||
Arithmetical Mode of Criteria in Multiple Non-Adjacent columns | Excel Worksheet Functions | |||
how do I filter for 1 variable in multiple columns | Excel Worksheet Functions | |||
Pivot Table combining multiple columns | Excel Discussion (Misc queries) |