LHumberto
New member
- Joined
- Jul 20, 2023
- Messages
- 3
- Reaction score
- 0
- Points
- 1
- Excel Version(s)
- Excel 365
I've been searching around but can't find a way to do this. Probably simple, but one of those that get me stuck...
I have a simple pivot with three columns. RoomID, IP-Address, Count of IP Address (indicates how many times that IP was used during a given date range)
On another tab, i have a list of people and the IP addresses that they have used during that same data range. I then use a xlookup to list by whom the IP was used, and Filter (with Transpose) to list all dates a given individual used an IP address. Because the same IP might appear in different rooms Filter is returning more than it should, as it is capturing the info from all rooms.
Note: using Excel 365, Classic PivotTable layout. Values below are just samples, not my real data.
Pivot (in orange below) is based on "Table 1"
"Who used the IP" uses the IP in a XLOOKUP (simple refence, e.g. "B2") to look on "Table 2"
If someone used the IP, that same IP (again simple reference to that same cell, "B2"), is used with Transpose/Filter to "look" back in "Table 1" and list when the IP was used (When Used column).
The problem is for Mary / IP 1.1.1.1 Transpose/ Filter is listing four dates, as it is capturing info from rooms 00001 & 00002. Two instance from room 00001 and two instances from room 00002.
NOTE: I understand that the dates listed are not the dates Mary used the IP. That info is not present on the users table (Table 2). Those are just the dates the IP was used. But I still need to filter for just the dates the IP was used in that room.
What I (think I) need is to identify which RoomID is associated with that IP (Mary's example, 00001 or 00002) and use that info as a primary Filter. I thought it should be simple to refence the RoomID column / group based on the current row, but I'm failing miserably. GETPIVOTDATA ain't my friend.
There might be a million other ways to do this (and thousands of them better), but my real question is:
Is it possible to make a refence to the Group RoomID (or any other "non-calculated" fields) in a pivot table?
Any pointers are greatly appreciated.
I have a simple pivot with three columns. RoomID, IP-Address, Count of IP Address (indicates how many times that IP was used during a given date range)
On another tab, i have a list of people and the IP addresses that they have used during that same data range. I then use a xlookup to list by whom the IP was used, and Filter (with Transpose) to list all dates a given individual used an IP address. Because the same IP might appear in different rooms Filter is returning more than it should, as it is capturing the info from all rooms.
Note: using Excel 365, Classic PivotTable layout. Values below are just samples, not my real data.
Pivot (in orange below) is based on "Table 1"
"Who used the IP" uses the IP in a XLOOKUP (simple refence, e.g. "B2") to look on "Table 2"
If someone used the IP, that same IP (again simple reference to that same cell, "B2"), is used with Transpose/Filter to "look" back in "Table 1" and list when the IP was used (When Used column).
The problem is for Mary / IP 1.1.1.1 Transpose/ Filter is listing four dates, as it is capturing info from rooms 00001 & 00002. Two instance from room 00001 and two instances from room 00002.
NOTE: I understand that the dates listed are not the dates Mary used the IP. That info is not present on the users table (Table 2). Those are just the dates the IP was used. But I still need to filter for just the dates the IP was used in that room.
What I (think I) need is to identify which RoomID is associated with that IP (Mary's example, 00001 or 00002) and use that info as a primary Filter. I thought it should be simple to refence the RoomID column / group based on the current row, but I'm failing miserably. GETPIVOTDATA ain't my friend.
There might be a million other ways to do this (and thousands of them better), but my real question is:
Is it possible to make a refence to the Group RoomID (or any other "non-calculated" fields) in a pivot table?
Any pointers are greatly appreciated.
| Room ID | IP | No of Times IP Used | Who Used the IP | When Used | |||
| 00001 | 1.1.1.1 | 2 | Mary | 03/10/2021 | 04/08/2021 | 07/15/2021 | 03/02/2022 |
| 2.2.2.2 | 2 | Mike | 01/30/2020 | 04/20/2020 | |||
| 3.3.3.3 | 5 | No | N/A | ||||
| 00002 | 1.1.1.1 | 2 | Mary | 03/10/2021 | 04/08/2021 | 07/15/2021 | 03/02/2022 |
| 4.4.4.4 | 2 | Paul | 05/23/2019 | 06/25/2019 | |||
| 6.6.6.6 | 3 | No | |||||
| 00003 | 2.2.2.2 | 1 | Mike | 01/30/2020 | 04/20/2020 | ||
| 7.7.7.7 | 3 | Steve | 03/22/2020 | 04/06/2020 | 10/11/2021 |
