Choosecols formula but need intervals with it.

[mabubakerali]

In the following Screenshot, i am using the choosecols formula in E6, And i am displaying three columns from another sheet in it, but what i want is thats the values in H column should be Displayed in column i, and i want choosecols formula which is in E6 to skip the column H in this sheet and display the results which are being displayed in H in to column i. Is there anyway with this chooscols formula to do it?

 

[hagia_sofia]

Hello, an easier bypass would probably be to select the data including the first empty column and then refer to it within CHOOSECOLS, i.e. something like

=CHOOSECOLS(1,2,3,10,4)

where 10 would be the number of a column with empty cells.

 

[mabubakerali]

Hello, an easier bypass would probably be to select the data including the first empty column and then refer to it within CHOOSECOLS, i.e. something like

=CHOOSECOLS(1,2,3,10,4)

where 10 would be the number of a column with empty cells.

Yup ,Currently I am doing this way, but i trying to find a better solution though. Thanks

 

[hagia_sofia]

I see. And what would you consider to be a better solution?

 

[mabubakerali]

I see. And what would you consider to be a better solution?

right now, the solution you provided is best, but i was thinking maybe something built in Choosecols formula that lets me skip a column in the answer sheet.

 

[Peter_SSs]

i.e. something like

=CHOOSECOLS(1,2,3,10,4)

where 10 would be the number of a column with empty cells.

If the extra column was empty cells, wouldn't that show up as a column of zeros (unless that extra column was filled with null strings)?

24 10 26.xlsm
	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
5	Amount	Cust	Contact	Email	Ref
6	51	90	52	0	29					51	90	52	29	90	42	85	80	75
7	90	79	62	0	56					90	79	62	56	12	91	28	83	79
8	70	30	46	0	38					70	30	46	38	12	78	51	77	89
9	10	95	87	0	78					10	95	87	78	62	81	37	24	8
10	39	20	43	0	7					39	20	43	7	51	19	26	6	28
11	90	26	50	0	18					90	26	50	18	5	33	94	2	97
12	53	60	11	0	3					53	60	11	3	10	51	21	6	48
13	85	57	11	0	44					85	57	11	44	23	14	87	16	77
14	99	75	65	0	89					99	75	65	89	30	82	23	3	34
15	86	63	70	0	5					86	63	70	5	85	55	81	5	29
Choosecols

Cell Formulas
Range	Formula
E6:I15	E6	=CHOOSECOLS(N6:W15,1,2,3,10,4)
Dynamic array formulas.

In any case I suspect that may not be an acceptable solution as I presume the Email column is wanted blank so that email addresses can be added later. If that was done on this sheet then the CHOOSECIOLS formula will turn into a #SPILL! error

Yup ,Currently I am doing this way,

Are you really? If so do you have a column of zeros like my example? .. or a column of blanks - unlike your image?

Are you intending to fill the email column in the sheet this formula is on? or would you be filling another column on the other sheet for emails?

 

[mabubakerali]

If the extra column was empty cells, wouldn't that show up as a column of zeros (unless that extra column was filled with null strings)?

24 10 26.xlsm
	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
5	Amount	Cust	Contact	Email	Ref
6	51	90	52	0	29					51	90	52	29	90	42	85	80	75
7	90	79	62	0	56					90	79	62	56	12	91	28	83	79
8	70	30	46	0	38					70	30	46	38	12	78	51	77	89
9	10	95	87	0	78					10	95	87	78	62	81	37	24	8
10	39	20	43	0	7					39	20	43	7	51	19	26	6	28
11	90	26	50	0	18					90	26	50	18	5	33	94	2	97
12	53	60	11	0	3					53	60	11	3	10	51	21	6	48
13	85	57	11	0	44					85	57	11	44	23	14	87	16	77
14	99	75	65	0	89					99	75	65	89	30	82	23	3	34
15	86	63	70	0	5					86	63	70	5	85	55	81	5	29
Choosecols

Cell Formulas
Range	Formula
E6:I15	E6	=CHOOSECOLS(N6:W15,1,2,3,10,4)
Dynamic array formulas.

In any case I suspect that may not be an acceptable solution as I presume the Email column is wanted blank so that email addresses can be added later. If that was done on this sheet then the CHOOSECIOLS formula will turn into a #SPILL! error



Are you really? If so do you have a column of zeros like my example? .. or a column of blanks - unlike your image?

Are you intending to fill the email column in the sheet this formula is on? or would you be filling another column on the other sheet for emails?

Simply go to Advance options of the worksheet and select "Display Nothing with cells Having zero" and column of zeroes will be gone

 

[Peter_SSs]

Simply go to Advance options of the worksheet and select "Display Nothing with cells Having zero" and column of zeroes will be gone

.. as would any other cells in the worksheet containing zeros. Perhaps you don't have any of those, but what about my other question?

Are you intending to fill the email column in the sheet this formula is on? or would you be filling another column on the other sheet for emails?

Having a column of empty-looking cells from a formula like this would not allow you to actually use those cells directly on the worksheet for anything else without causing a spill error.

 

[Peter_SSs]

You didn't actually answer my first question to you either

Are you really? If so do you have a column of zeros like my example? .. or a column of blanks - unlike your image?

If you were already doing what had been suggested then you would not have needed to be asking how to move the data from column H to column I and we would have been able to see in your image the apparently empty column of (hidden) zero values.