# Help Me Eliminate a Column from My Worksheet

#### kelsoz

##### New Member
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.

Last edited:

### Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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

That would be:

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

Bingo - Thanks Andrew!

Replies
3
Views
108
Replies
16
Views
719
Replies
4
Views
311
Replies
2
Views
188
Replies
5
Views
539

1,206,971
Messages
6,075,925
Members
446,170
Latest member
zzzz02

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back