Solved How to ignore blanks when copying data using formulas

December 11, 2015 at 02:51:58
Specs: Windows 7
Hi
I want to copy the contents of cell A1 in to a separate worksheet based on cell A2 having a value in it. that bit is simple enough using the below formula but I then want it to not look at the blanks when copying into the new sheet so that the data is in one complete list without spaces.

Useing the formula as below does the copying but but also copies the blanks and creates gaps on the new sheet which is what I want to avoid

=IF(ISTEXT('Training by Role'!C5),'Training by Role'!B5,"")

any help gratefully received

thanks

message edited by Peeteee


See More: How to ignore blanks when copying data using formulas

Report •


✔ Best Answer
December 11, 2015 at 10:15:59
Even though I am not clear on the desired output (based on your example data) perhaps this will work for you. We're going to use a "Helper Column"

Start with the data you posted in Response #4.

Use the formula you posted in your OP to create the extracted list - with the blanks - in another sheet. This is your "Helper Column". You can put the list anywhere you want.

Next, name the column where your placed the list, using the Name "BlanksRange". Set the scope of the Name to the sheet where the list exists, not to the workbook. If you set the scope of the Named Range to a single worksheet, then you can use the same Name (and therefore the same formula) in each sheet. In other words, there is no need to use a different name in each sheet, you just have to create the named Range in each sheet and set it's scope to just that sheet.

Now you can hide the Helper Column (the Named Range) if you want.

Finally, use Chip Pearson's array formula on the Helper Column (the Named Range) to create a list without the blank cells. Do this in each sheet.

=IFERROR(INDEX(BlanksRange,SMALL((IF(LEN(BlanksRange),
ROW(INDIRECT("1:"&ROWS(BlanksRange))))),ROW(A1)),1),"")

Because the name "BlanksRange" will only refer to the Named Range in the sheet in which you created the "worksheet scope" Named Range, each sheet will display the correct list based on that sheet's Helper Column.

Keep in mind that an Array Formula must be entered with Ctrl-Shift-Enter for it to work. You can Ctrl-Shift-Enter the formula in the first cell and then drag it down as far as you need. Excel will know to create an array formula in each cell as you drag.

I start with this in Sheet1

             A                         B
1          Safety                    Op Mgr
2     Abrasive Wheels	
3     Additive Bags Disposal          x
4     ADR
5     Asbestos Awareness              x	
6     ATEX Awareness                  x
7     Behavioural Safety Training     x
8     BOAS - Cat 2 Steam Boilers	
9     BOAS Boiler Operator            x
10    Confined Space Entry            x
11    Permit to Work                  x


In Sheet2, your ISTEXT formula creates this list. Column A is Named
BlanksRange using the worksheet scope.

                 A
1              Safety
2
3      Additive Bags Disposal
4      
5      Asbestos Awareness
6      ATEX Awareness
7      Behavioural Safety Training
8      
9      BOAS Boiler Operator
10     Confined Space Entry
11     Permit to Work

Finally, using Chip Pearson's formula on that Named Range, I get:

              B
1          Safety
2      Additive Bags Disposal
3      Asbestos Awareness
4      ATEX Awareness
5      Behavioural Safety Training
6      BOAS Boiler Operator
7      Confined Space Entry
8      Permit to Work

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03



#1
December 11, 2015 at 06:33:31
Assuming that you are using Excel 2007 or later, you should be able to adapt the IFERROR formula found about half way down this page, below the screenshot of the cells containing the letters.

http://www.cpearson.com/EXCEL/NoBla...

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
December 11, 2015 at 07:41:14
Thanks DarbyDad,

I have been playing with the formula you linked too and just cant seem to get it right in my head (or on the sheet) in order to work out what i need to do, i've never used array formula before so am just trying to feel my way through it but will let you know if i have any joy.


Report •

#3
December 11, 2015 at 08:06:41
It's not clear to me what issues you are having, so perhaps it would help you posted a short example og your data, including both the input data and the desired output.

Please click on the following link and read the instructions on how to use the pre tags to post example data in this forum. Make sure you include Row numbers and Column letters in your example so that it is easier for us to understand your data layout.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
December 11, 2015 at 08:32:08
Hi,

so this is part of the front sheet of a training matrix that lists all training requirements and all job positions. The x indicates that the training is required by the job holder.

 Safety		            Op Mgr	Ast mgr	  Prod planner
Abrasive Wheels 				
Additive Bags Disposal		x	x	
ADR				
Asbestos Awareness		x	x	   x
ATEX Awareness		        x	x	
Behavioural Safety Training     x       x	   x
BOAS - Cat 2 Steam Boilers  				
BOAS Boiler Operator 		x	x	
Confined Space Entry		x	x	   x
Permit to Work 		        x	x	   x

What I then want to do is create a records sheet for all of the training based on the matrix where only the relevant training titles are populated into a sheet

Operations Manager	
	
	
Training Description	              Training Category
	
	
ATEX Awareness	                          H&S
Behavioural Safety Training	          H&S
	
BOAS Boiler Operator training (1 Day)     H&S
Confined Space Entry	                  H&S
Contractors Authority & Permit to Work 	  H&S
COSHH Awareness	                          H&S

Because the operations manager dosen't require abrasive wheel or ADR training the formula I posted earlier brings through a blank row which is what I want to avoid

Hope this helps

thanks for your assistance


Report •

#5
December 11, 2015 at 08:55:23
I am assuming that "Additive Bags Disposal" and "Permit to Work" should have been in your output. Is that correct?

In addition, I don't see these items in the Input but they are in output. Why is that?

Contractors Authority & Permit to Work 	  H&S
COSHH Awareness	                          H&S

P.S. You did not include Row numbers or Column letters as requested.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#6
December 11, 2015 at 09:16:56
The more I look at your output, the less clear I am of what you are trying to do.

Your Output has training descriptions that are not in your input and there are x's in the Op Mgr column for training descriptions that are not carried over to your output.

Please clarify.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#7
December 11, 2015 at 10:12:38
Perhaps a simpler solution would be to Delete all the Blank Rows,
on your second sheet, after you have copied them.

Try this

2007 Excel - Delete Blank Rows,

First highlight all the Columns with your data in the cells, IE A, B, C, etc.

Next on the Ribbon
Select Find & Select
Select Go To

this should bring up the Go To window,

Select Special
Select Blanks

Click OK

Next on the Ribbon
In the Cells Section,
Click Delete

All your blank rows should now be gone, leaving only those rows that contained data.

MIKE

http://www.skeptic.com/


Report •

#8
December 11, 2015 at 10:15:59
✔ Best Answer
Even though I am not clear on the desired output (based on your example data) perhaps this will work for you. We're going to use a "Helper Column"

Start with the data you posted in Response #4.

Use the formula you posted in your OP to create the extracted list - with the blanks - in another sheet. This is your "Helper Column". You can put the list anywhere you want.

Next, name the column where your placed the list, using the Name "BlanksRange". Set the scope of the Name to the sheet where the list exists, not to the workbook. If you set the scope of the Named Range to a single worksheet, then you can use the same Name (and therefore the same formula) in each sheet. In other words, there is no need to use a different name in each sheet, you just have to create the named Range in each sheet and set it's scope to just that sheet.

Now you can hide the Helper Column (the Named Range) if you want.

Finally, use Chip Pearson's array formula on the Helper Column (the Named Range) to create a list without the blank cells. Do this in each sheet.

=IFERROR(INDEX(BlanksRange,SMALL((IF(LEN(BlanksRange),
ROW(INDIRECT("1:"&ROWS(BlanksRange))))),ROW(A1)),1),"")

Because the name "BlanksRange" will only refer to the Named Range in the sheet in which you created the "worksheet scope" Named Range, each sheet will display the correct list based on that sheet's Helper Column.

Keep in mind that an Array Formula must be entered with Ctrl-Shift-Enter for it to work. You can Ctrl-Shift-Enter the formula in the first cell and then drag it down as far as you need. Excel will know to create an array formula in each cell as you drag.

I start with this in Sheet1

             A                         B
1          Safety                    Op Mgr
2     Abrasive Wheels	
3     Additive Bags Disposal          x
4     ADR
5     Asbestos Awareness              x	
6     ATEX Awareness                  x
7     Behavioural Safety Training     x
8     BOAS - Cat 2 Steam Boilers	
9     BOAS Boiler Operator            x
10    Confined Space Entry            x
11    Permit to Work                  x


In Sheet2, your ISTEXT formula creates this list. Column A is Named
BlanksRange using the worksheet scope.

                 A
1              Safety
2
3      Additive Bags Disposal
4      
5      Asbestos Awareness
6      ATEX Awareness
7      Behavioural Safety Training
8      
9      BOAS Boiler Operator
10     Confined Space Entry
11     Permit to Work

Finally, using Chip Pearson's formula on that Named Range, I get:

              B
1          Safety
2      Additive Bags Disposal
3      Asbestos Awareness
4      ATEX Awareness
5      Behavioural Safety Training
6      BOAS Boiler Operator
7      Confined Space Entry
8      Permit to Work

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#9
December 14, 2015 at 03:16:34
Hi

dispite my clearly dodgy info and data you have achivied exactly what i wanted to do.

thanks verty much for that

Peetee


Report •


Ask Question