Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- Home
- :
- Excel
- :
- General Discussion
- :
- Data Filtering by Column/ Horizontal

Discussion Options

- Subscribe to RSS Feed
- Mark Discussion as New
- Mark Discussion as Read
- Pin this Discussion for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-30-2020 08:10 AM

I have date in 1st column and names in 2nd, 3rd, 4th column and so on. How can I filter a data to get the count of each name in the specified date range.

**Enclosed excel file with data and sample outcome required.**

Labels:

14 Replies

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-30-2020 08:49 AM

@sandeeptikait Here is a formula that works:

`=SUMPRODUCT(--($B$2:$B$16>=$L$3)*($B$2:$B$16<=$N$3),(--($C$2:$C$16=$K6)+($D$2:$D$16=$K6)+($E$2:$E$16=$K6)+($F$2:$F$16=$K6)+($G$2:$G$16=$K6)+($H$2:$H$16=$K6)))`

I wonder if @Sergei Baklan or someone else might chime in with a 'better' answer.

I'm pretty sure some of those new array functions like FILTER could help out here.

Maybe I'll get access to them some day...

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-30-2020 12:42 PM

@mtarler , IMHO - "best solution" is quite individual and depends on criterion. From my point of view that's any one which works and which concrete person understands better. Thus using such pattern potentially the person could save a lot of time on deployment and maintenance. That in particular means that for the same person today one solution could be considered as best, and in a while another one, assuming both do exactly the same job. Sorry for some philosophy, with your experience that could be not new.

@sandeeptikait , if play with modern Excel functionality, formula could be

```
=SUM(--(
INDEX(Range,
SEQUENCE( COUNTIFS(INDEX(Range,0,1),">="&$L$3,INDEX(Range,0,1),"<="&$N$3),1,2),
SEQUENCE(1,COLUMNS(Range)-1,2,1)
)=K6)
)
```

where the "Range" is named range for entire source data. If extract unique names and return counts as spill it'll be more complicated.

Most probably other formula (without Power Query) solutions exist.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-30-2020 12:47 PM

@sandeeptikait Offering a PowerQuery approach, using three Named Ranges (names, Start and End). Although I still consider myself a relative newbie at the subject, I'm rather pleased with the outcome :), but at the same time convinced that it probably can be done better.

Results are shown in the small table in N5:O11. Change the Start and/or End dates. Select the Data ribbon and press"Refresh All" for an updated result.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-30-2020 02:31 PM - edited 06-30-2020 02:36 PM

This is another modern dynamic array solution. One form that I like is to use the beta-release function LET to generate a self-documenting formula

```
= LET(
firstRow, XLOOKUP(Start, Date, NameRange, ,1),
finalRow, XLOOKUP(End, Date, NameRange, ,-1),
filteredRange, firstRow:finalRow,
COUNTIFS(filteredRange, Name) )
```

It uses the fact that XLOOKUP returns a range to build the filtered range to search. Without LET, one would have

`= COUNTIFS(XLOOKUP(Start,Date,NameRange, ,1):XLOOKUP(End,Date,NameRange, ,-1), Name)`

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-30-2020 10:57 PM

@mtarler Thanks for your response. You solution for counting the number of occurrence is fine. But how would I get the unique records of names, if there are hundreds of names in the list.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-30-2020 11:39 PM

@Peter Bartholomew Thanks for your response. You solution for counting the number of occurrence is fine. But how would I get the unique records of names, if there are hundreds of names in the list.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-01-2020 01:43 AM

Looks good to me. About the only point where I would differ is in that I would have converted the named range to a Table before starting with PQ. That both makes the input dynamic and saves the promote headers step in the M code.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-01-2020 02:09 AM

The UNIQUE function requires the table to be unpivoted and that is a somewhat messy process.

```
= LET(
m, ROWS(NameTable),
n, COLUMNS(NameTable)-1,
k, SEQUENCE(m*n),
rowNum, 1+QUOTIENT(k-1,n),
colNum, 1+MOD(k-1,n),
list, INDEX(NameTable, rowNum, 1+colNum),
SORT(UNIQUE(list)) )
```

Without the LET function, the process is worse. One would either have to implement each line as a separate named formula using Name Manager or, worse still, write it as a nested formula.

There are (paid) function libraries that can help. Charles Williams's FastExcel includes an UNPIVOT function which reduces the process of determining a sorted list of distinct names to

```
= LET(
unpivoted, UNPIVOT(NameTable,,1,,,0),
list, INDEX(unpivoted, ,3),
SORT(UNIQUE(list)) )
```

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-01-2020 04:22 AM

@Peter Bartholomew Good point. Will keep that in mind!

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-01-2020 09:42 AM

Even without LET() your code will be very similar, only with global names.

My variant is

```
=UNIQUE(
INDEX(Range,
MOD(SEQUENCE((COLUMNS(Range)-1)*(ROWS(Range)-1))-1,14)+2,
INT( SEQUENCE((COLUMNS(Range)-1)*(ROWS(Range)-1),,(ROWS(Range)-1)*2)/
(ROWS(Range)-1))
)
)
```

More interesting is spill for counts, here LET could be more useful. Without it

```
=TRANSPOSE(
MMULT(
SEQUENCE(1,(COLUMNS(Range)-1)*(ROWS(Range)-1),,0),
(
INDEX(Range,
MOD(SEQUENCE((COLUMNS(Range)-1)*(ROWS(Range)-1))-1,14)+2,
INT( SEQUENCE((COLUMNS(Range)-1)*(ROWS(Range)-1),,(ROWS(Range)-1)*2)/
(ROWS(Range)-1))
)=TRANSPOSE(K6#))*
(INDEX(Range, MOD(SEQUENCE((COLUMNS(Range)-1)*(ROWS(Range)-1))-1,14)+2, 1 )>=$L$3)*
(INDEX(Range, MOD(SEQUENCE((COLUMNS(Range)-1)*(ROWS(Range)-1))-1,14)+2, 1 )<=$N$3)
)
)
```

Not sure how to combine both spills together.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-01-2020 02:39 PM

First an observation. Anything that can be done with LET could be done with named formula, I have used such calculations for years. What you lose is the 'calculate once, use many times'; that would require helper ranges and not defined names.

The following gives both columns from a single spill but I wouldn't care to make any claims concerning computational efficiency!

```
= LET(
\1, "Filter range by date",
firstRow, XLOOKUP(Start, NameTable[Date], NameTable, ,1),
finalRow, XLOOKUP(End, NameTable[Date], NameTable, ,-1),
filteredNames, firstRow:finalRow,
\2, "Calculate frequencies for filtered table",
freqTable, COUNTIFS(filteredNames, filteredNames),
\3, "Unpivot both tables",
m, ROWS(filteredNames),
n, COLUMNS(filteredNames)-1,
k, SEQUENCE(m*n),
rowNum, 1+QUOTIENT(k-1,n),
colNum, 1+MOD(k-1,n),
nameList, INDEX(filteredNames, rowNum, 1+colNum),
freqList, INDEX(freqTable, rowNum, 1+colNum),
\4, "Combine name and frequency lists",
combined, IF({1,0}, nameList, freqList),
\5, "Sort unique on combined name and frequencies",
SORT(UNIQUE(combined)) )
```

My indentation is not as interesting as yours because, in the main, I have used local names in place of nested formulas. The jury is probably still out on whether one __should__ write formulas like this; the fact that one can still has some shock value at the moment!

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-01-2020 02:58 PM

I have nothing against LET() in general except it is now only on beta channel. Thus it could be used mainly for modelling purposes, but not on practice for real projects.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-03-2020 04:37 AM

My use of LET is pure self-indulgence; though I am particularly interested in developing techniques that exploit the new function. Before installing Office 365, I used to use named formula to bypass the dreaded implicit intersection. That process still works, but it is now more convenient to develop formulas using LET and, also, the parameters are then presented in a logical sequence, so providing self-documenting formulas. Paste Names was all very well but the logic didn't exactly shine through.

For client work, I would clearly not be able to use LET. A contract that required direct referencing and non-array solutions, it would simply turn down; if that is what is want, they can do it themselves

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-03-2020 08:41 PM

@sandeeptikait Here is a formula for finding unique names without the new functions (I hope it is ok that it isn't alphabetical):

`=IFERROR(OFFSET($A$1,MOD(AGGREGATE(15,7, (ROW($C$3:$H$16)+100000*COLUMN($C$3:$H$16))/(--(COUNTIF(J$5:J10,$C$3:$H$16)=0)), 1),100000)-1,INT(AGGREGATE(15,7, (ROW($C$3:$H$16)+100000*COLUMN($C$3:$H$16))/(--(COUNTIF(J$5:J10,$C$3:$H$16)=0)), 1)/100000)-1),"")`