Multiple reference against running serial no in excel

[silambarasan.J]

Sir,

How do Running Serial No against corresponding to the Multiple/twice range.

Starting Serial is 500:

NAME DOC NO DATE CBB TRAN Runing Serial No Req
AHMED 380713 24/06/2015 75 IXPS 500
AHMED 380720 24/06/2015 IXPS
AHMED 380720 24/06/2015 IXPS
AHMED 380836 24/06/2015 IXPS
AHMED 380715 24/06/2015 75 IXPS 501
AHMED 380836 24/06/2015 IXPS
AHMED 380846 24/06/2015 IXPS
AHMED 381001 24/06/2015 IXPS
AHMED 381185 24/06/2015 IXPS
AHMED 381193 24/06/2015 IXPS
BANGA 380713 24/06/2015 11 XPRESS 502
BANGA 380717 24/06/2015 XPRESS
BANGA 380722 24/06/2015 XPRESS
BANGA 380835 24/06/2015 11 IXPS 503
BANGA 380846 24/06/2015 IXPS
BANGA 381004 24/06/2015 25 IXPS 504
BANGA 381193 24/06/2015 IXPS

please help me.

file attached.

[JohnTopley]

You need explain how the serial numbers are "generated".

It appears to be on change of CBB? so rows 3 to 6 are serial number 500. etc ?

[JohnTopley]

If my assumption above is correct then put the following in F4 and copy down

=IF(D4="",F3,F3+1)

[Jacc]

Like this?

[silambarasan.J]

JohnTopley Sir,

thanks for your reply.

but i need Name,Doc No,Date,Cbb,Tran(column A to E) are same, the value return is 500 in column F3.(Starting Serial is 500)

am trying this formula in F3=IF(COUNTIFS($A$3:A3,A3,$C$3:C3,C3,$D$3:D3,D3,$E$3:E3,E3)=1

but i don't know whats the another step.

please help me.

file attached.

[silambarasan.J]

Jacc Sir,

love it.

but i need the answer my required format if possible.

please help me.

[Tony Valko]

Like this?

Sorry for off-topic interjection:

Although there is no official rule regarding this behavior, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

Thanks again for all your hard work here!

[Jacc]

Ok. I'm not sure what you mean silambarasan.J. If you include the result you want in your workbook and post it I will understand.

[silambarasan.J]

Tony Valko sir.

apologetic for the mistakes.am struggling to explanation about of thread because English is not my main language.

in future never it happen again.sorry for the inconvenience.

i can follow up Jacc Sir post.here some internal issue so i cannot reply on time.

[oeldere]

@silambarasan.J

The reply of Tony Valko was to Jacc (see #7).

You can proceed with this question, but Jacc ask you in #8 to add the result in your file.

You get better help on your question if you add a small excel file, without confidential information.

Please also add manualy the expected result in your file.

[silambarasan.J]

Thanks oeldere sir for again consider of my query.(#10)

Jacc sir thanks you so much again for the reply. yes i can understand.

The Reply its based on serial No and returning the value row wise.

its an reversible of compare of my data. i want column wise.

as requested to you its convert of my required data if possible.its very helpful.


A B C D E F

Branch DOC NO DATE CBB TRAN Runing Serial No Req

AHMED 380713 24/06/2015 75 IXPS 500
AHMED 380720 24/06/2015 IXPS
AHMED 380720 24/06/2015 IXPS
AHMED 380836 24/06/2015 IXPS

AHMED 380715 24/06/2015 18 IXPS 501
AHMED 380836 24/06/2015 IXPS
AHMED 380846 24/06/2015 IXPS
AHMED 381001 24/06/2015 IXPS
AHMED 381185 24/06/2015 IXPS
AHMED 381193 24/06/2015 IXPS

BANGA 380713 24/06/2015 11 XPRESS 502
BANGA 380717 24/06/2015 XPRESS
BANGA 380722 24/06/2015 XPRESS

BANGA 380835 24/06/2015 11 IXPS 503
BANGA 380846 24/06/2015 IXPS


The above mentioned details same was i have updated in excel daily basis.

Header of : Branch,Date,Cbb,Transport Name(Column A,C,D,E) are same i have put the serial No in column F2.

In put the serial No first cells only against corresponding to the reference.

Note:24/06/2015 the day end of serial No is 550.

The next day starting the running serial No is 551 against corresponding to the reference.

The same process i have follow up next branch/day. This my routine work.

I have lot of line item,minimum 25000 in each month.

it very difficult to change the serial manually in every time/day.

My query is whenever multiple range/reference(Column A,C,D,E) are same i want running serial number in first cells only.(F2 Only)

other cells is blanks.

sorry for the late reply.

please help me.file attached.

[oeldere]

H3 =if(A2="",H1+1,H2)

Why is F17 = 503 ?

See the attached file.

[silambarasan.J]

oeldere Sir,

wrongly entered.

H3 =if(A2="",H1+1,H2)

this formula consider in single column only.its working all of ascending order only.

in case data are Descending Order value return is error.also blanks cells against serial no return.

My query is whenever multiple range/reference(Column A,C,D,E) are same i want running serial number in first cells only.(F2 Only)


i want corresponding to the range against serial No.

please help me.

[Jacc]

silambarasan.J , you posted the same file again, it does not help.

You have to make a few examples manually so that we can see what the finished result will look like.
Please fill in the result for the first 10 rows manually and post the workbook again.

[oeldere]

H3 =if(A2="",H1-1,H2)

If this is not what you expect, add a new file, with all criteria, and show us how the cells are related, e.g. with a color.

Add manualy the expected result in your file and add also the text with it that that is the expected result.

[silambarasan.J]

Jacc & oeldere sir,

thanks you so much. i can followup your advice and suggestion.

[cyiangou]

If I look at the data, it appears that a new serial number is generated when CBB changes, regardless of the other columns. So:

• Enter 500 in cell F1
• Enter '=IF($D3<>"",IF(ROWS($A$3:$A3)=1,$F$1,MAX($F$2:$F2)+1),"")' in F3 and copy down

serial_incr.jpg

[silambarasan.J]

cyiangou Sir,

thank you so much for your help.

[oeldere]

If that is the criteria (#17) , the below formula will do (simular as already offered).

H1 = 499

H2 =if(D2<>"",H1+1,H1) and drag down.

Please reply.

[Jacc]

Sorry silambarasan.J, I didn't understand that was the solution. Next time just mark it yellow or something.
Here is my version:

Please Login or Register  to view this content.

Edit: I now see that my solution is not just a bit like cyiangou's solution, it's exactly like it! Oh well, you certainly beat me to it cyiangou.

[silambarasan.J]

Oledere & jacc sir lot of thanks for each and every reply.write now I don't have a system.my mobile not supported xls/xlm.please give me some time