Extraction Question

[Guest]

Could you please help me with this matter?

The column found came from a text file.
Number Call test Found
1 t gg 6
2 t rr 10
3 u de 14
4 j ty 20
5 y hu
6 u ed
7 j gt
8 d vf
9 b ki
10 h yh
11 k bg
12 u ed
13 t sw
14 t rt
15 g mk
16 n lo
17 h uy
18 g hg
19 h rt
20 m vf

Then I want to get the information in another sheet which correspond under
the column Call and Test, as shown below:
Found Call test
6 u ed
10 h yh
14 t rt
20 m vf

I will appreciate your helping.
Thanks in advance.
Maperalia

 

[Guest]

I forgot to ask if this procedure can be done with any excel's formula.
Thanks.
Maperalia

 

[T. Valko]

I'm assuming that under the header "Found" are the numbers 6, 10, 14, 20?

assume the table on Sheet1 is in the range A121 (row 1 are column headers)

Enter this formula on Sheet2 in cell A2:

=IF(Sheet1!D2="","",Sheet1!D2)

Enter this formula on Sheet2 in cell B2 and copy across to cell C2:

=IF($A2="","",VLOOKUP($A2,Sheet1!$A$2:$C$21,COLUMNS(Sheet1!$A:B),0))

Select cells A2:C2 and copy down until you get blanks.

Biff

 

[T. Valko]

Here's a sample file:

sample_lookup.xls 15kb

http://cjoint.com/?mmuQLYWJHo

Biff

 

[T. Valko]

Here's an updated sample file:

http://cjoint.com/?mneAXpQFoi

Biff

 

[Guest]

Valko;
Thank you very much.. It is running perfectly!!!
I really appreciate your giving your support.

Maperalia

 

[T. Valko]

For which formula?

#N/A means no match was found. If the data "looks" like it matches you may
have data type mismatches where numbers are really TEXT and/or you may have
leading/trailing spaces that can't be seen. Those are the most common
problems.

Biff

 

[T. Valko]

Try this:

Select the range of cells that contain the formulas.
Goto the menu Edit>Replace
Find what: =
Replace with: =
Replace All

Basically, you're replacing the equal sign with the equal sign.

Biff

 

[T. Valko]

You're welcome. Thanks for the feedback!

Biff

Valko;
Thank you very much.. You are the best!!!!!

Maperalia

 

[Guest]

Valko;
Thanks for your help..
I wonder if you can give me one last advice.

I created a template sheet with the formulas you gave me (VLookup,Index).
Everything is working perfectly when I type manually the data of the table
which will be read it by these formulas and format it properly.
However, when I copy a data from another sheet and paste it in my template
sheet I got the "#N/A" in all the cells. I have formatted the fonts again
after is been pasted it but I still have the same "#N/A" message. So, I
decided to retype the same data manually and is working very well. I noticed
that is happen only with the column which the formula is been vlookup and/or
match.

Could you please tell me what could be the problem?

Thnaks in advance.
Maperalia
Well