Help Me Eliminate a Column from My Worksheet

[kelsoz]

Hi. I'm trying tom simplify something that works OK now. I'm new here so I'm not sure how to show you what's up, so I'll add a screen capture of Excel with a simplified presentation of the situation. I use 2002 but have 2013 as well.

So I have column A9:A20 of with anything in it, but there is a unique occurrence of a, b, and c.. I have a, b, and c listed in A4, 5 and 6. What I want in B4, 5, and 6 is the worksheet row numbers of the a, b, and c entries in the A9:A20. To do this I add column B9:B20 cells with the formula "=ROW()". Then in B4 I have "=VLOOKUP(A4,$A$9:$B$20,2)" and I get 12 - and so on for b and c. Exactly right. This is shown under "Current Solution".


-- removed inline image ---
Whoops - my screen capture didn't take - What's the best way for me to show my spreadsheet?

What I want is to get the values in B4, 5, and 6 without the cells B9:B20. Seems silly to create these cells. This is shown under "Wanted Solution".

My question is: What goes in F4, 5, 6?

Thanks for any help.

 

[Andrew Poulsom]

You can use the HTMLMaker here to post a snapshot of your worksheet:

https://skydrive.live.com/?cid=8cffdec0ce27e813&sc=documents&id=8CFFDEC0CE27E813%21189

 

[kelsoz]

And here is the spreadsheet capture missing in my orig post. Thanks for the tip Andrew.

Sheet1

*	A	B	C	D	E	F	G
1	*	*	*	*	*	*	*
2	Current Solution	*	*	Wanted Sulution	*
3	*	*	*	*	*	*	*
4	a	12	=VLOOKUP(A4,$A$9:$B$20,2)	*	a	12	<<< What goes here???
5	b	15	*	*	b	15	*
6	c	18	*	*	c	18	*
7	----------	----------	*	*	----------	*	*
8	*	*	*	*	*	*	*
9	0	9	=ROW()	*	0	*	*
10	0	10	*	*	0	*	*
11	0	11	*	*	0	*	*
12	a	12	*	*	a	*	*
13	0	13	*	*	0	*	*
14	0	14	*	*	0	*	*
15	b	15	*	*	b	*	*
16	0	16	*	*	0	*	*
17	0	17	*	*	0	*	*
18	c	18	*	*	c	*	*
19	0	19	*	*	0	*	*
20	0	20	*	*	0	*	*
21	*	*	*	*	*	*	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:47px;"><col style="width:47px;"><col style="width:189px;"><col style="width:35px;"><col style="width:47px;"><col style="width:51px;"><col style="width:156px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Andrew Poulsom]

That would be:

=MATCH(A4,$A$9:$A$20,FALSE)+ROWS($A$1:$A$8)

 

[kelsoz]

Bingo - Thanks Andrew!