Entry Number

[Shabbo]

Dear Sir,

I wanted to get entry number like Payment/011 then Payment/012 then Payment/013

But data is not continue there many blank columns are coming in between if I hide them again its not working entry number is getting for blank column as well.

How to resolve this ?

 

[Naresh Yadav]

Hi,

Pls use this one and finish the formula with CTRL+SHIFT_ENTER

IFERROR(INDEX($A$3:$A$8,SMALL(IF($A$3:$A$8>"0",ROW($A$3:$A$8)-2),ROW(A1))),"")

 

[Naresh Yadav]

OR Without array Formula..

=LOOKUP(ROW(A1),COUNTIF(OFFSET($A$2,,,ROW($3:$9)-2,),"<>"&"")+1,$A$3:$A$8)&""

 

[Naresh Yadav]

Hi,

one more option that can also be used in this case..
it is a Non-Array formula which no needs to use CTRL+SHIFT+ENTER

=IFERROR(INDEX($A$3:$A$8,MATCH(0,INDEX(COUNTIF($G$2:G2,$A$3:$A$8&""),,),0)),"")

 

[NARAYANK991]

Hi ,

Yet another option.

Narayan

 

[Shabbo]

Hi ,

Yet another option.

Narayan

Dear Sir,

Can you please help to apply formula for attached sheet.

 

[Naresh Yadav]

Hi,

pls share your desire result along with your data structure in your excel file so that it can be eased for us to understand what kind of result you are expecting..

regards
Naresh

 

[Shabbo]

Hi,

pls share your desire result along with your data structure in your excel file so that it can be eased for us to understand what kind of result you are expecting..

regards
Naresh

Dear Sir,

Attached is my actual working sheet, I have copied some data because data is large.

Please help to apply formula in attached sheet.

 

[xlstime]

Hi Shabbo,

PFA the solution with simple concatenate and count function

=IF(A5="","",CONCATENATE("Cpay/",1159+COUNT($A$5:A5)))

Dear Sir,

Attached is my actual working sheet, I have copied some data because data is large.

Please help to apply formula in attached sheet.

 

[Naresh Yadav]

Hi,
Apply this one in your H column
you can increase the highlighted Blue area as long as you can
your large data can make your system slow post applying this formula, so Pls keep patience and let the formula complete
=IFERROR(INDEX($B$5:$B$229,MATCH(0,INDEX(COUNTIF($H$4:H4,$B$5:$B$229&""),,),0)),"")

 

[Shabbo]

Hi,
Apply this one in your H column
you can increase the highlighted Blue area as long as you can
your large data can make your system slow post applying this formula, so Pls keep patience and let the formula complete
=IFERROR(INDEX($B$5:$B$229,MATCH(0,INDEX(COUNTIF($H$4:H4,$B$5:$B$229&""),,),0)),"")

Thanks for your reply,

But its not working properly I want entry number wherever TO CASH IN HAND has mentioned.

 

[Naresh Yadav]

Than you can use above formula..
IF(A5="","",CONCATENATE("Cpay/",1159+COUNT($A$5:A5))) advise given by Mr.XLSTIME

OR

IF(ISNUMBER(SEARCH("cash in hand",E5)),"CPAY/"&1159+COUNT($A$5:A5),"")

OR
=IF(ISBLANK(A5),"","CPay/"&1159+COUNT($A$5:A5))