Hello!
I will be grateful for your help on how to skip blank spaces when copying values to another tab.
For example, on Tab1 of the Workbook I have a column, starting from A1:
23
blank
34
blank
54
blank
On Tab2 I copy the value of A1, and then I need consecutively copy all other values, i.e. 34 and 54, in a row, ignoring all blanks.
Is there a formula I can use for such purpose?
Thank you very much for your help!
Are you saying that you want to copy odd rows only?
If so put this in A1 and fill down.
Formula:
Please Login or Register to view this content.
Last edited by mehmetcik; 06-29-2017 at 08:48 PM.
My General Rules if you want my help. Not aimed at any person in particular:
1. Please Make Requests not demands, none of us get paid here.
2. Check back on your post regularly. I will not return to a post after 4 days.
If it is not important to you then it definitely is not important to me.
Hi, It's my first time to provide answer in this forum. I'm usually the one who asks for help. Given your scenario above, can you try this?
Last edited by dirtythetoy; 06-29-2017 at 08:05 PM.
No. As I said, I want to make sure the formula takes only numbers, and ignores blanks.Originally Posted by mehmetcik
Are you saying that you want to copy odd rows only?
If so put this in A1 and fill down.
Formula:Please Login or Register to view this content.
Thank you, but nope. As I said, I need to do that consecutively, ignoring all blanks, thus I need to have numbers now in a row (they were in a column), one after the other, no blanks at all.
Here is what I tried to do:
I used the ISBLANK formula, kindly suggested by dirtythetoy, but I can't copy values from column to a row.
If I don't fix the column cell, then columns change as I drag the formula throughout the row;
if I fix the name of the column, C, then I get the same value - the number of rows doesn't change.
Please, take a look at the file attached.
How about this?
=INDEX($C$2:$C$10,MATCH(COLUMN(A1)-1,$B$2:$B$10,0))
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
For future reference, although there is no official rule regarding this behaviour, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.
I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.
Thanks again for all your hard work here!
Thank you very much. It does the trick. But I don't understand how. Please, help me to understand what is happening "underneath the hood" here.
Here is what I don't understand:
(1) MATCH function returns the position of a specified value in the range. So:
COLUMN(A1)-1 returns 0, therefore MATCH looks for zero value in the range B2:B10;
but why does it return the position of B2, which is a 1-st row in the range, or do we start counting from 0 when it comes to ranges in Excel?
I know that in all other computing spheres we always start from 0, but here when it comes to columns, A1 is returned as the 1st column, not 0th, according to the this formula.
Next, As I "drag" the formula along rows from B to F, COLUMN(A1)-1 changes to respective columns, and therefore COLUMN(B1)-1 returns 1, COLUMN(C1)-1 returns 2, COLUMN(D1)-1 returns 3, COLUMN(E1)-1 returns 4).
MATCH, as I understand it, looks for positions of these values in the range B2:B10. Does it mean that it ignores blanks, and takes into account only rows that contain values, and when it returns 4, for example, it doesn't mean that it returns a 4th row of the sheet, but the 4th position of the give value?
(2) INDEX takes the range as the first argument, and then looks for the value, which is pointed to by the number of the row, returned by the MATCH function.
I don't understand how it works here: if MATCH returns 4, then shouldn't INDEX read this as the "row number 4 in the range C2:C10", and return zero, as that forth row in the range is a blank cell; as another example: if MATCH returns 3, why INDEX returns a correct value of 890,000, and not 60,000, which is on the 3d row in the C2:C10, where C2 = 750K, C3 is blank, C4 = 60,000?
Sorry for these questions, but I am truly interested in understanding how things work.
Thank you very much!
=INDEX($C$2:$C$10,MATCH(COLUMN(A1)-1,$B$2:$B$10,0))
=index({750000,"",60000,"",890000,"",560000,"",67000}, MATCH(column(A1)-1,{0,"",1,"",2,"",3,"",4},0)
the main reason for this column(A1)-1 is to get the number change when move to right. (ie, 0,1,2,3,4)
Column (A1) = column(R1C1), column function only take the value after C, so even
Column (A100) = column(R100C1), the value still 1,
but when more to right,
column(B1), =column(R1C2), hence the value is 2.
match will not ignore blank.Does it mean that it ignores blanks, and takes into account only rows that contain values, and when it returns 4, for example, it doesn't mean that it returns a 4th row of the sheet, but the 4th position of the give value?
match is return first match value position of the number in the range(which assume B2 as 1st), for example,
=match (1,{0,"",1,"",2,"",3,"",4},0)
it match the 3rd value, hence it return value as 3.
if you change column(A1)-1 to "", the result is 2.
then Index use the value of 3 as row postion, and return the 3rd value in the array. (ie, {750000,"",60000,"",890000,"",560000,"",67000}, and assume C2 as 1st value)
hence, the 3rd value is the 60000
hope it clarify.
you may try to change the number in B2 to 3, it will see how the result change. (but copy to a new file first to avoid error)
Last edited by BoredWorker; 06-30-2017 at 04:15 AM.
Hope you can learn every time you visit here.
If you still confuse on how it work, kindly ask or go to
i) Formula - Formula (Ribbon) > Formula Auditing (Section) > Evaluate Formula > Evaluate; or
ii) VBA/Code - Click F8 to see how it work step by step.
It it take care of your question, Please:
Mark tread as [Solved] [Thread Tools->Mark thread as Solved];and
Click *Add Reputation to thank anyone solved your question.
=INDEX($C$2:$C$10,MATCH(COLUMN(A1)-1,$B$2:$B$10,0))
You have nearly explained this in your post:
As you drag the formula across the MATCH value (COLUMN(A1)-1 has values of 0,1,2,3 etc
These are MATCHed against column B: whenever a match is found the value in C from the corresponding row is selected.
So your values of 0,1,2,etc are in rows 2,4,6 etc
So for COLUMN(A1)-1 (0) will match with B2 (0) : MATCH will return a value of 1 as it is the first (relative) row in the specified range (B2:B10) so we return the value in C2
(Ranges have to match).
For COLUMN(B1)-1 =1 , we will match with B4 (1) which is "relative" row 3 so we get value in C4.
To see this in "action":
Click on a cell with the formula, then click "Formulas", then "Evaluate Formulas". Click on "Evaluate" and you will how the formula calculates.
If that is all that is required then:-
Formula:Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks