Find the missing number

[Anbuselvam K]

Hi,

As per below pasted details, I've heading of Sales / Free sample in column B and Invoice Number / SR No. in column C. Sales have separate invoice series numbers and free sample have separate SR No. in Column C.

I want to find out the missing number in the column C, Please find the attached sheet for your information. In Sales missing number is 11 and Free Sample missing number is 1674. I need a display result in Column C, C4. (As per attached sheet)

Please do the needful and Thanks in Advance!


Sl.No Sales / Free Sample Invoice Number / SR No.
1 Sales 1
2 Sales 2
3 Sales 3
4 Free Sample 1665
5 Free Sample 1666
6 Sales 4
7 Sales 5
8 Sales 6
9 Free Sample 1667
10 Free Sample 1668
11 Free Sample 1669
12 Free Sample 1670
13 Free Sample 1671
14 Sales 7
15 Sales 8
16 Sales 9
17 Free Sample 1672
18 Free Sample 1673
19 Free Sample
20 Free Sample
21 Sales 10
22 Sales
23 Sales 12
24 Free Sample
25 Free Sample
26 Free Sample
27 Free Sample
28 Free Sample
29 Sales 13
30 Sales 14
31 Sales 15
32 Free Sample
33 Free Sample
34 Free Sample
35 Free Sample
36 Free Sample
37 Sales 16
38 Sales 17
39 Sales 18
40 Sales 19
41 Sales 20
42 Sales 21
43 Sales 22
44 Sales 23
45 Free Sample 1675
46 Free Sample
47 Free Sample
48 Free Sample
49 Free Sample
50 Free Sample 1676
51 Free Sample 1677
52 Free Sample 1678
53 Free Sample
54 Free Sample
55 Free Sample
56 Free Sample
57 Free Sample
58 Free Sample
59 Free Sample
60 Free Sample
61 Free Sample 1679
62 Free Sample 1680

 

[NARAYANK991]

Hi ,

See if this is OK.

Narayan

 

[Anbuselvam K]

Mr Narayan,

Thanks for your reply!

As per attached i got the results but, i need a results in Single cell Column C, C4. For getting the result i will give you one more tips, Sales number should be 1 to 1000 and the free sample number should be above 1000. So by using this can we avoid the helper columns because already i have 2.31MB capacity excel file which is getting hanged frequently.

So please do the needful!

 

[NARAYANK991]

Hi ,

First , please get over the assumption that using helper cells or helper columns will increase the recalculation time or that the recalculation time is always dependent on the file size ; you can have a very big file size , and the recalculation time may be small. You can use big formulae / complex formulae which avoid helper cells , and find that your recalculation time has actually increased.

See the attached file.

Narayan

 

[Anbuselvam K]

Mr Narayan

Thanks for your valuable suggestion and quick reply. I got the results.

If you don't mind, Can you suggest which configuration Desktop PC is required to use higher capacity excel file without hanging? Increasing RAM will help?

My present one is Windows10, i3, 3.4GHz Processor with 4GB RAM and 32bit. Excel 2013.

Your valuable reply will be highly appreciated. Thanks in Advance.

 

[Deepak]

Check this...in C4

Array so Ctrl+Shift+Enter

="Sales - " & COUNTIF(OFFSET(B6,,,MATCH(0,IF($B$6:$B$67="Sales ",$C$6:$C$67),0)),"Sales ")+1-1&" , Free Sample - " & COUNTIF(OFFSET(B6,,,MATCH(0,IF($B$6:$B$67="Free Sample",$C$6:$C$67),0)),"Free Sample")+MIN(IF(($B$6:$B$67="Free Sample")*($C$6:$C$67>0),$C$6:$C$67))-1

 

[Anbuselvam K]

Mr Deebak

Thanks for your reply!


I got the results with this array formula but its showing only the first missing number. For example as per attached, in sales missing numbers are 2, 3 and 11. but its showing only 2.

I hope you understand! please do the needful.

 

[NARAYANK991]

Hi ,

As far as I can see , your hardware configuration is adequate.

I have no idea how Excel 2013 compares with Excel 2007 ; if you can try the same workbook using Excel 2007 , we can get an idea of whether Excel 2013 is more resource intensive than Excel 2007.

Do you make use of any of the new features which are available in Excel 2013 ?

Narayan

 

[Deepak]

Mr Deebak

Thanks for your reply!


I got the results with this array formula but its showing only the first missing number. For example as per attached, in sales missing numbers are 2, 3 and 11. but its showing only 2.

I hope you understand! please do the needful.

Yes that's the drawbak of the same.
You may follow narayan approach or VBA would be alternative.

 

[Anbuselvam K]

Okay Thanks.

 

[Anbuselvam K]

Mr Narayan

Yes i'm using Pivot table, pivot chart, slicers and time lines which is not in the 2007 version.

 

[Naresh Yadav]

Hi,

Another Option that can be used to yield your desire result
pls find the formula with CTRL+SHIFT+ENTER

=IF($B$6:$B$67=$B$6,SMALL(IF(COUNTIF($C$6:$C$67,ROW(INDIRECT($E$1&":"&$E$2)))=0,ROW(INDIRECT($E$1&":"&$E$2))),ROW(A1)))

 

[Anbuselvam K]

Dear yadav,

Again i'm finding the first missing number only. See the attached file with your and Mr Deebak Suggested formula.

 

[Naresh Yadav]

pls check the attached file and let me know if you still facing the issue..
have tried it in your file and it has given the correct result


regards
Naresh

 

[Anbuselvam K]

Expected result in Column C, C4 as below.

Sales - 2 , 3 , 11 Free Sample - 1672 , 1673 , 1674