table of columns into a single column ignoring blanks

[planetsair]

hello

I have data in columns (B, C & D) and I would like to combine it all into one column (F) but ignoring the blank cells (my actual table has 50 columns of 10 rows)

I have got this far (see pic below) with this formula
=OFFSET($B$3, MOD(ROW()-ROW($F$1),ROWS($B$3:$B$12)), TRUNC((ROW()-ROW($F$1))/ROWS($B$3:$B$12)),1,1)

but I dont know how to make it ignore the blank cells - can anyone help me please ?
cheers

 

[Fluff]

Is your data just numbers, or do you have long text strings?
Also what version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[planetsair]

Hi Fluff
thanks for the advice - i have updated as suggested (windows and excel 365 for this formula).

the data is mainly 4 characters plus 4 numbers (e.g. ABCD1234) but could be all numbers (it is not likely to be all characters).

thanks for your help

 

[Fluff]

Ok, thanks for that.
How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I
1
2		List1	List2	List3	List4	List5	List6		Combined
3		1058	3746	3899	3338	3214	4749		1058
4		3130	4182	3102	2908	3429	1078		3746
5		3557	1165	1856	2819	3438	4500		3899
6		3386	4608	2870	2677	3430	4671		3338
7		1038	3749	1303	3470	4163	2178		3214
8		4733	3883	3070	3816	3620			4749
9		1180	3578	2005	1474	3215			3130
10		4174		2488	3555	1412			4182
11		1181			4521				3102
12					4336				2908
13									3429
14									1078
15									3557
16									1165
17									1856
18									2819
19									3438
20									4500
21									3386
22									4608
23									2870
24									2677
25									3430
26									4671
27									1038
28									3749
29									1303
30									3470
31									4163
32									2178
33									4733
34									3883
35									3070
36									3816
37									3620
38									1180
39									3578
40									2005
41									1474
42									3215
43									4174
44									2488
45									3555
46									1412
47									1181
48									4521
49									4336
Result

Cell Formulas
Range	Formula
I3:I49	I3	=FILTERXML("<k><m>"&TEXTJOIN("</m><m>",,B3:G13)&"</m></k>","//m")
Dynamic array formulas.

 

[planetsair]

thanks fluff,
your suggestions are much appreciated but its not quite there yet - the formula you have suggested has given me the list1 in hte correct order but has not joined List2/3/etc onto/below list1. Can you help a bit more please ?

I have updated my document - hopefully its a little clearer now (see pic)

 

[Fluff]

Can you post your sample data including formulae rather than just an image.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[planetsair]

sorry - I dont have permission to download the addon to my work PC (I am waiting on IT to get back to me)

=OFFSET($B$3, MOD(ROW()-ROW($G$3),ROWS($B$3:$B$12)), TRUNC((ROW()-ROW($G$3))/ROWS($B$3:$B$12)),1,1)
this formula is in G3 to G32

 

[Fluff]

What formula is in F3?

 

[planetsair]

this formula ....
=FILTERXML("<k><m>"&TEXTJOIN("</m><m>",,$B$3:$D$12)&"</m></k>","//m")

 

[Fluff]

Do you have the Sort or Unique functions?