Count down numbering with Vlookup?

Alphacsulb

Alphacsulb

Active Member
Joined
Mar 20, 2008
Messages
414
Hi Excel Lovers,
I'm looking for a way to automate my numbering in column K.

Here is a sample of my data:
Column K is where I want the count down numbering to go.
Column C determines whether the next cell up in column K continues to count down.
Column E determines how many fit in a box.

I attempted to write a formula but cannot begin to understand what the logic would be pickup the last number in column K that had similar data in column C. I'm not sure if a formula is the way to go about it, or whether it should be a macro. You can see my bad attempt below.

*ABCDEFGHIJK
PERM*************************SNGLPT38 **
PERM*************************SNGLPT38 **
PERM*************************SNGLPT38 **
PERM***********AUTO**5-DIGIT 95948T6 **
PERM***********AUTO**5-DIGIT 95948T6 **
PERM***********AUTO**5-DIGIT 95948T7 **
PERM***********AUTO**5-DIGIT 96122T28 **
PERM***********AUTO**5-DIGIT 96122T28 **
PERM***********AUTO**5-DIGIT 96122T28 **
PERM***********AUTO**5-DIGIT 96122T28 **
PERM***********AUTO**5-DIGIT 96122T28 **
PERM***********AUTO**5-DIGIT 96122T28 **
PERM**********AUTO**MIXED AADC 926T1 **
PERM**********AUTO**MIXED AADC 926T1 **
PERM**********AUTO**MIXED AADC 926T1 **
PERM**********AUTO**MIXED AADC 926T1 **
PERM**********AUTO**MIXED AADC 926T1 **
PERM**********AUTO**MIXED AADC 926T1 **
PERM**********AUTO**MIXED AADC 926T1 *e
PERM*************************SNGLPT38 *e
PERM*************************SNGLPT38 *e
PERM*************************SNGLPT38 *e
PERM***********AUTO**5-DIGIT 95947T2 *e
PERM***********AUTO**5-DIGIT 95948T6 *
PERM***********AUTO**5-DIGIT 95948T6 *
PERM***********AUTO**5-DIGIT 95948T6 *
PERM***********AUTO**5-DIGIT 95948T6 *
PERM***********AUTO**5-DIGIT 95948T6 *
PERM***********AUTO**5-DIGIT 95948T6 *
PERM***********AUTO**5-DIGIT 95948T6 *
PERM***********AUTO**5-DIGIT 95948T6 *
PERM***********AUTO**5-DIGIT 95948T6 *
PERM***********AUTO**5-DIGIT 95948T6 *
PERM***********AUTO**5-DIGIT 95948T6 *
PERM***********AUTO**5-DIGIT 95948T6 *
PERM***********AUTO**5-DIGIT 95948T6 *

<colgroup><col style="width:30px; "><col style="width:67px;"><col style="width:34px;"><col style="width:211px;"><col style="width:58px;"><col style="width:139px;"><col style="width:151px;"><col style="width:155px;"><col style="width:49px;"><col style="width:72px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]6789[/TD]

[TD="align: right"]1-14[/TD]

[TD="align: right"]6788[/TD]
[TD="align: right"]165[/TD]
[TD="align: right"]52[/TD]

[TD="align: right"]735100[/TD]
[TD="align: right"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6790[/TD]

[TD="align: right"]1-14[/TD]

[TD="align: right"]6789[/TD]
[TD="align: right"]165[/TD]
[TD="align: right"]56[/TD]

[TD="align: right"]939069[/TD]
[TD="align: right"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6791[/TD]

[TD="align: right"]1-14[/TD]

[TD="align: right"]6790[/TD]
[TD="align: right"]165[/TD]
[TD="align: right"]163[/TD]

[TD="align: right"]111320[/TD]
[TD="align: right"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6792[/TD]

[TD="align: right"]1-14[/TD]

[TD="align: right"]6791[/TD]
[TD="align: right"]249[/TD]
[TD="align: right"]86[/TD]

[TD="align: right"]104485[/TD]
[TD="align: right"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6793[/TD]

[TD="align: right"]1-14[/TD]

[TD="align: right"]6792[/TD]
[TD="align: right"]249[/TD]
[TD="align: right"]232[/TD]

[TD="align: right"]103915[/TD]
[TD="align: right"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6794[/TD]

[TD="align: right"]1-14[/TD]

[TD="align: right"]6793[/TD]
[TD="align: right"]249[/TD]
[TD="align: right"]45[/TD]

[TD="align: right"]938900[/TD]
[TD="align: right"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6795[/TD]

[TD="align: right"]1-14[/TD]

[TD="align: right"]7361[/TD]
[TD="align: right"]270[/TD]
[TD="align: right"]192[/TD]

[TD="align: right"]933626[/TD]
[TD="align: right"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6796[/TD]

[TD="align: right"]1-14[/TD]

[TD="align: right"]7362[/TD]
[TD="align: right"]270[/TD]
[TD="align: right"]193[/TD]

[TD="align: right"]933945[/TD]
[TD="align: right"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6797[/TD]

[TD="align: right"]1-14[/TD]

[TD="align: right"]7363[/TD]
[TD="align: right"]270[/TD]
[TD="align: right"]202[/TD]

[TD="align: right"]111434[/TD]
[TD="align: right"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6798[/TD]

[TD="align: right"]1-14[/TD]

[TD="align: right"]7379[/TD]
[TD="align: right"]270[/TD]
[TD="align: right"]247[/TD]

[TD="align: right"]938413[/TD]
[TD="align: right"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6799[/TD]

[TD="align: right"]1-14[/TD]

[TD="align: right"]7380[/TD]
[TD="align: right"]270[/TD]
[TD="align: right"]248[/TD]

[TD="align: right"]938364[/TD]
[TD="align: right"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6800[/TD]

[TD="align: right"]1-14[/TD]

[TD="align: right"]7381[/TD]
[TD="align: right"]270[/TD]
[TD="align: right"]249[/TD]

[TD="align: right"]938324[/TD]
[TD="align: right"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6801[/TD]

[TD="align: right"]1-14[/TD]

[TD="align: right"]7382[/TD]
[TD="align: right"]97[/TD]
[TD="align: right"]6[/TD]

[TD="align: right"]622170[/TD]
[TD="align: right"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6802[/TD]

[TD="align: right"]1-14[/TD]

[TD="align: right"]7383[/TD]
[TD="align: right"]97[/TD]
[TD="align: right"]52[/TD]

[TD="align: right"]108113[/TD]
[TD="align: right"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6803[/TD]

[TD="align: right"]1-14[/TD]

[TD="align: right"]7384[/TD]
[TD="align: right"]97[/TD]
[TD="align: right"]53[/TD]

[TD="align: right"]107942[/TD]
[TD="align: right"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6804[/TD]

[TD="align: right"]1-14[/TD]

[TD="align: right"]7385[/TD]
[TD="align: right"]97[/TD]
[TD="align: right"]57[/TD]

[TD="align: right"]111240[/TD]
[TD="align: right"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6805[/TD]

[TD="align: right"]1-14[/TD]

[TD="align: right"]7386[/TD]
[TD="align: right"]97[/TD]
[TD="align: right"]67[/TD]

[TD="align: right"]934109[/TD]
[TD="align: right"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6806[/TD]

[TD="align: right"]1-14[/TD]

[TD="align: right"]7387[/TD]
[TD="align: right"]97[/TD]
[TD="align: right"]68[/TD]

[TD="align: right"]110372[/TD]
[TD="align: right"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6807[/TD]

[TD="align: right"]1-14[/TD]

[TD="align: right"]7388[/TD]
[TD="align: right"]97[/TD]
[TD="align: right"]74[/TD]

[TD="align: right"]103128[/TD]
[TD="align: right"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6808[/TD]

[TD="align: right"]1-17[/TD]

[TD="align: right"]7389[/TD]
[TD="align: right"]165[/TD]
[TD="align: right"]111[/TD]

[TD="align: right"]104766[/TD]
[TD="align: right"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6809[/TD]

[TD="align: right"]1-17[/TD]

[TD="align: right"]7390[/TD]
[TD="align: right"]165[/TD]
[TD="align: right"]112[/TD]

[TD="align: right"]385500[/TD]
[TD="align: right"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6810[/TD]

[TD="align: right"]1-17[/TD]

[TD="align: right"]7391[/TD]
[TD="align: right"]165[/TD]
[TD="align: right"]118[/TD]

[TD="align: right"]108250[/TD]
[TD="align: right"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6811[/TD]

[TD="align: right"]1-17[/TD]

[TD="align: right"]7392[/TD]
[TD="align: right"]270[/TD]
[TD="align: right"]74[/TD]

[TD="align: right"]935289[/TD]
[TD="align: right"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6812[/TD]

[TD="align: right"]1-17[/TD]

[TD="align: right"]7441[/TD]
[TD="align: right"]249[/TD]
[TD="align: right"]91[/TD]

[TD="align: right"]103877[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"]242[/TD]

[TD="bgcolor: #cacaca, align: center"]6813[/TD]

[TD="align: right"]1-17[/TD]

[TD="align: right"]7442[/TD]
[TD="align: right"]249[/TD]
[TD="align: right"]92[/TD]

[TD="align: right"]937697[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"]243[/TD]

[TD="bgcolor: #cacaca, align: center"]6814[/TD]

[TD="align: right"]1-17[/TD]

[TD="align: right"]7443[/TD]
[TD="align: right"]249[/TD]
[TD="align: right"]93[/TD]

[TD="align: right"]413750[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"]244[/TD]

[TD="bgcolor: #cacaca, align: center"]6815[/TD]

[TD="align: right"]1-17[/TD]

[TD="align: right"]7444[/TD]
[TD="align: right"]249[/TD]
[TD="align: right"]94[/TD]

[TD="align: right"]111296[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"]245[/TD]

[TD="bgcolor: #cacaca, align: center"]6816[/TD]

[TD="align: right"]1-17[/TD]

[TD="align: right"]7445[/TD]
[TD="align: right"]249[/TD]
[TD="align: right"]95[/TD]

[TD="align: right"]763200[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"]246[/TD]

[TD="bgcolor: #cacaca, align: center"]6817[/TD]

[TD="align: right"]1-17[/TD]

[TD="align: right"]7446[/TD]
[TD="align: right"]249[/TD]
[TD="align: right"]96[/TD]

[TD="align: right"]760150[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"]247[/TD]

[TD="bgcolor: #cacaca, align: center"]6818[/TD]

[TD="align: right"]1-17[/TD]

[TD="align: right"]7447[/TD]
[TD="align: right"]249[/TD]
[TD="align: right"]97[/TD]

[TD="align: right"]107354[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"]248[/TD]

[TD="bgcolor: #cacaca, align: center"]6819[/TD]

[TD="align: right"]1-17[/TD]

[TD="align: right"]7448[/TD]
[TD="align: right"]249[/TD]
[TD="align: right"]98[/TD]

[TD="align: right"]103331[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"]249[/TD]

[TD="bgcolor: #cacaca, align: center"]6820[/TD]

[TD="align: right"]1-17[/TD]

[TD="align: right"]7449[/TD]
[TD="align: right"]249[/TD]
[TD="align: right"]99[/TD]

[TD="align: right"]938829[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]6821[/TD]

[TD="align: right"]1-17[/TD]

[TD="align: right"]7450[/TD]
[TD="align: right"]249[/TD]
[TD="align: right"]100[/TD]

[TD="align: right"]752187[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]6822[/TD]

[TD="align: right"]1-17[/TD]

[TD="align: right"]7451[/TD]
[TD="align: right"]249[/TD]
[TD="align: right"]101[/TD]

[TD="align: right"]750530[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]6823[/TD]

[TD="align: right"]1-17[/TD]

[TD="align: right"]7452[/TD]
[TD="align: right"]249[/TD]
[TD="align: right"]102[/TD]

[TD="align: right"]742150[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]6824[/TD]

[TD="align: right"]1-17[/TD]

[TD="align: right"]7453[/TD]
[TD="align: right"]249[/TD]
[TD="align: right"]103[/TD]

[TD="align: right"]735350[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"]5[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
K6807=IF(AND(C6807=C6808,K6808=1),E6807,IF(AND(C6807=C6808,K6808<=E6807),K6808-1,"e"))
K6808=IF(AND(C6808=C6809,K6809=1),E6808,IF(AND(C6808=C6809,K6809<=E6808),K6809-1,"e"))
K6809=IF(AND(C6809=C6810,K6810=1),E6809,IF(AND(C6809=C6810,K6810<=E6809),K6810-1,"e"))
K6810=IF(AND(C6810=C6811,K6811=1),E6810,IF(AND(C6810=C6811,K6811<=E6810),K6811-1,"e"))
K6811=IF(AND(C6811=C6812,K6812=1),E6811,IF(AND(C6811=C6812,K6812<=E6811),K6812-1,"e"))
K6812=IF(AND(C6812=C6813,K6813=1),E6812,IF(AND(C6812=C6813,K6813<=E6812),K6813-1,"e"))
K6813=IF(AND(C6813=C6814,K6814=1),E6813,IF(AND(C6813=C6814,K6814<=E6813),K6814-1,"e"))
K6814=IF(AND(C6814=C6815,K6815=1),E6814,IF(AND(C6814=C6815,K6815<=E6814),K6815-1,"e"))
K6815=IF(AND(C6815=C6816,K6816=1),E6815,IF(AND(C6815=C6816,K6816<=E6815),K6816-1,"e"))
K6816=IF(AND(C6816=C6817,K6817=1),E6816,IF(AND(C6816=C6817,K6817<=E6816),K6817-1,"e"))
K6817=IF(AND(C6817=C6818,K6818=1),E6817,IF(AND(C6817=C6818,K6818<=E6817),K6818-1,"e"))
K6818=IF(AND(C6818=C6819,K6819=1),E6818,IF(AND(C6818=C6819,K6819<=E6818),K6819-1,"e"))
K6819=IF(AND(C6819=C6820,K6820=1),E6819,IF(AND(C6819=C6820,K6820<=E6819),K6820-1,"e"))
K6820=IF(AND(C6820=C6821,K6821=1),E6820,IF(AND(C6820=C6821,K6821<=E6820),K6821-1,"e"))
K6821=IF(AND(C6821=C6822,K6822=1),E6821,IF(AND(C6821=C6822,K6822<=E6821),K6822-1,"e"))
K6822=IF(AND(C6822=C6823,K6823=1),E6822,IF(AND(C6822=C6823,K6823<=E6822),K6823-1,"e"))
K6823=IF(AND(C6823=C6824,K6824=1),E6823,IF(AND(C6823=C6824,K6824<=E6823),K6824-1,"e"))
K6824=IF(AND(C6824=C6825,K6825=1),E6824,IF(AND(C6824=C6825,K6825<=E6824),K6825-1,"e"))

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4

The desired results would be starting at K6789 down to K6811:

[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]160[/TD]
[/TR]
[TR]
[TD="align: right"]161[/TD]
[/TR]
[TR]
[TD="align: right"]162[/TD]
[/TR]
[TR]
[TD="align: right"]239[/TD]
[/TR]
[TR]
[TD="align: right"]240[/TD]
[/TR]
[TR]
[TD="align: right"]241[/TD]
[/TR]
[TR]
[TD="align: right"]265[/TD]
[/TR]
[TR]
[TD="align: right"]266[/TD]
[/TR]
[TR]
[TD="align: right"]267[/TD]
[/TR]
[TR]
[TD="align: right"]268[/TD]
[/TR]
[TR]
[TD="align: right"]269[/TD]
[/TR]
[TR]
[TD="align: right"]270[/TD]
[/TR]
[TR]
[TD="align: right"]91[/TD]
[/TR]
[TR]
[TD="align: right"]92[/TD]
[/TR]
[TR]
[TD="align: right"]93[/TD]
[/TR]
[TR]
[TD="align: right"]94[/TD]
[/TR]
[TR]
[TD="align: right"]95[/TD]
[/TR]
[TR]
[TD="align: right"]96[/TD]
[/TR]
[TR]
[TD="align: right"]97[/TD]
[/TR]
[TR]
[TD="align: right"]163[/TD]
[/TR]
[TR]
[TD="align: right"]164[/TD]
[/TR]
[TR]
[TD="align: right"]165[/TD]
[/TR]
[TR]
[TD="align: right"]270[/TD]
[/TR]
</tbody>[/TABLE]

Any leads would be appreciated.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
I can't really tell by your formula or your brief explanation at the top what you are trying to do. Your data starts in Row 6789, but your example formulas start at 6807 and the results you want start at 6789.

It might be simpler if you just explain in simple terms not formula terms how I would look at your data and come to the answer of 160 for your first result and 161 for your second result etc and perhaps I could help you.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
VLOOKUP?
Replies
1
Views
222
MeaclH
M
SteveP29
  • Solved
Golf Skins
Replies
3
Views
597
Fluff
Fluff
A
  • Solved
Lookup PAAF ID based on Worker ID and date range
Replies
3
Views
128
Fluff
Fluff
J
  • Solved
Auto count 1-12 with changing a a number (4-11) every 12 rows.
Replies
3
Views
195
Fluff
Fluff
M
  • Solved
Count cells that are contain multiple criteria (dates)
Replies
3
Views
321
Fluff
Fluff

Forum statistics

Threads
1,224,802
Messages
6,181,048
Members
453,014
Latest member
Chris258

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top