Searching multiple columns for data

[MikeJ100]

Hi, in table 1 shows how the data is presented to me, whilst table 2 shows how I want to have the data. How can I do this? I don't know any VBA.

Thanks in advance for your help.

Table 1

Coln A	Coln B	Coln C	Coln D	Coln E	Coln F	Coln Z
Mike			100
John						50
Bill				70

<tbody>
</tbody>

Table 2

Coln A	Coln B
Mike	100
John	50
Bill	70

<tbody>
</tbody>

 

[DKaur]

You could just highlight all blank cells and delete them (using "shift cells left"). This is assuming you don't want to track the original column the value was in.

 

[AlanY]

try

Excel 2012
	A	B	C	D	E	F	G
1	Table 1
2	Coln A	Coln B	Coln C	Coln D	Coln E	Coln F	Coln Z
3	Mike			100
4	John						50
5	Bill				70
6
7
8	Table 2
9	Coln A	Coln B
10	Mike	100
11	John	50
12	Bill	70
Sheet1

Cell Formulas
Range	Formula
B10	=LOOKUP(90000000000,B3:G3,B3:G3)

 

[MikeJ100]

Thank you for your help, it's very much appreciated.

What does the 90000000000 do, as when I try to use it on text, it comes up as #N/A?

 

[AlanY]

it can be any large number larger than any in your data range so that the lookup() function report the next number smaller than that.

if you've a mixed of number and text like this, try

Excel 2012
	A	B	C	D	E	F	G
1	Table 1
2	Coln A	Coln B	Coln C	Coln D	Coln E	Coln F	Coln Z
3	Mike			100
4	John						Check
5	Bill				70
6
7
8	Table 2
9	Coln A	Coln B
10	Mike	100
11	John	Check
12	Bill	70
Sheet1

Cell Formulas
Range	Formula
B10	=IFERROR(INDEX(B3:G3,MATCH(9.99999999999999E+307,B3:G3)),INDEX(B3:G3,MATCH(REPT("z",255),B3:G3)))

 

[MikeJ100]

Thank you

 

[AlanY]

you're welcome