Array formula not recognizing alphanumeric data naming system - how to fix?

kelle

Board Regular
Joined
Apr 1, 2015
Messages
93
I'm having some trouble with a spreadsheet that I previously asked for help with here. Our business is rehauling its internal records management systems and I've been working on a spreadsheet to help track everything. Basically, right now I have a spreadsheet with a total of 281 individual records, some of which are in the same box. As different types of records have different retention schedules, I have a formula in column N that spits out the maximum retention date depending on if the box number is unique or not (if not, it calculates the maximum retention date).

What I needed help with was determining the numbers in O1-3. O1 is the active records count, so all records that have not been destroyed, minus O2, which is a count of all cells in D with any value entered into them. However, those represent individual records, and I need to know how many total boxes we still have on site, which needs to include a calculation for any boxes that have multiple records in it, like box 1817 below.

I got help here for the array formula in O3, which looks like this:
=SUM(IF(FREQUENCY(IF(NOT(ISNUMBER(O5:O5000)), D5:D5000, ""), IF(NOT(ISNUMBER(O5:O5000)), D5:D5000, ""))>0, 1, 0))-1

and it worked fine until I went through another huge file room of records and realized that the box naming convention had changed to include leading alphabetical characters (eg. FA-2007-17). The formula will only count any item that is a number and ignores any of the boxes with the alphanumeric naming convention.

Any help on how to change the formula to account for alphanumeric and numeric box titles?

vTygQNX.png


Thanks so much for any help you can offer me! I never realized how powerful Excel was until I started wading through these forums.
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

RickXL

MrExcel MVP
Joined
Sep 9, 2013
Messages
4,328
Hi,

You could use this formula in O3:

=SUM(IF(FREQUENCY(IF(LEN(D5:D5000)>0,MATCH(D5:D5000,D5:D5000,0),""), IF(LEN(D5:D5000)>0,MATCH(D5:D5000,D5:D5000,0),""))>0,1))

Note, it must be used as an array formula. That is, you paste it into the cell then select the formula in the formula bar and hit Ctrl+Shift+Enter.

Formula pinched from here: https://support.office.com/en-sg/ar...plicates-8d9a69b3-b867-490e-82e0-a929fbc1e273

Regards,
 

kelle

Board Regular
Joined
Apr 1, 2015
Messages
93
Hi,

You could use this formula in O3:

=SUM(IF(FREQUENCY(IF(LEN(D5:D5000)>0,MATCH(D5:D5000,D5:D5000,0),""), IF(LEN(D5:D5000)>0,MATCH(D5:D5000,D5:D5000,0),""))>0,1))

Note, it must be used as an array formula. That is, you paste it into the cell then select the formula in the formula bar and hit Ctrl+Shift+Enter.

Formula pinched from here: https://support.office.com/en-sg/ar...plicates-8d9a69b3-b867-490e-82e0-a929fbc1e273

Regards,

That works to get the total number, but does not recognize when anything's been inputted into any cells in column O, which is my main concern. Thank you, though!
 

RickXL

MrExcel MVP
Joined
Sep 9, 2013
Messages
4,328
Hi again,

Sorry, I didn't realise that you neede to know O1 and O2 as well. try this:


Excel 2013
DMNO
19
22
38
4File/Box #
51621
6181701/01/2015
71817
8FA-2007-17
99801/01/2015
1099
11dfg
12dfg
13123
14234
15234
16
Sheet1
Cell Formulas
RangeFormula
O1=COUNTA(D5:D5000)-O2
O2=COUNTA(O5:O5000)
O3{=SUM(IF(FREQUENCY(IF(LEN(D5:D5000)>0,MATCH(D5:D5000,D5:D5000,0),""), IF(LEN(D5:D5000)>0,MATCH(D5:D5000,D5:D5000,0),""))>0,1))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 

kelle

Board Regular
Joined
Apr 1, 2015
Messages
93
Hi again,

Sorry, I didn't realise that you neede to know O1 and O2 as well. try this:

Excel 2013
DMNO
19
22
38
4File/Box #
51621
6181701/01/2015
71817
8FA-2007-17
99801/01/2015
1099
11dfg
12dfg
13123
14234
15234
16

<colgroup><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
O1=COUNTA(D5:D5000)-O2
O2=COUNTA(O5:O5000)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
O3{=SUM(IF(FREQUENCY(IF(LEN(D5:D5000)>0,MATCH(D5:D5000,D5:D5000,0),""), IF(LEN(D5:D5000)>0,MATCH(D5:D5000,D5:D5000,0),""))>0,1))}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Unfortunately that just gets me the total amount of boxes, not the amount minus any destroyed boxes. Thank you though!
 

RickXL

MrExcel MVP
Joined
Sep 9, 2013
Messages
4,328
Which cell is supposed to have the number of remaining boxes in?
 

kelle

Board Regular
Joined
Apr 1, 2015
Messages
93
O3. I noticed though when I was reading through the formula that there's no reference included to O5:O5000, which I'm imagining is the problem, since it doesn't cross-reference against any boxes that are marked in O as destroyed.
 

kelle

Board Regular
Joined
Apr 1, 2015
Messages
93
Got it working in one way or another--

{=SUM(IF(FREQUENCY(IF(LEN(O5:O5000)>0,MATCH(D5:D5000,D5:D5000,0),""), IF(LEN(O5:O5000)>0,MATCH(D5:D5000,D5:D5000,0),""))>0,1))} gets me the total number of destroyed boxes, so I just entered 2 separate formula into a hidden cell, one for the total amount and then that one, and deducted the second from the first. Thanks so much for all your help!
 

RickXL

MrExcel MVP
Joined
Sep 9, 2013
Messages
4,328
Hi again,

I don't like giving up!

I think this formula might be the one you are looking for:

=SUM(IFERROR(NOT(ISBLANK($D$5:$D$5000))*ISBLANK($O$5:$O$5000)/(COUNTIFS($D$5:$D$5000,$D$5:$D$5000,$O$5:$O$5000,"="&"")),0))

It is an "array formula" so needs to be entered with a Ctrl+Shift+Enter.

It is based on this idea:

=SUM(1/COUNTIF(A1:A6,A1:A6))

See this for an explanation: Count Unique Values in Excel - Easy Excel Tutorial

The trick was to exclude files with dates against them from the count. This was done by changing the COUNTIF into a COUNTIFS and using the second criterion to ignore those entries.
 

RickXL

MrExcel MVP
Joined
Sep 9, 2013
Messages
4,328
I converted the data in columns D to O into a Table (Insert-->Tables-->Table).
The default column names used in the table can then be used in the formula in place of the D5:D5000 range for instance.

Another option is to base a Dynamic Named Range on the table Columns. For instance, in the Name Manager you can define a Named Range called Disp. I selected the cells from the Table using the mouse and the definition looked like this: =Table1[Disposition Date].

I did the same with the Boxes column, I called it Box.
Now Box and Disp will automatically expand with the data as more rows are added.

I also made the assumption that entries in the Box column would not ever be blank.

The formula can now be written as:

=SUM(IFERROR(ISBLANK(Disp)/(COUNTIFS(Box,Box,Disp,"="&"")),0))
 

Forum statistics

Threads
1,181,947
Messages
5,932,918
Members
436,869
Latest member
ABGTH

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
Top