Jump to content

Wikipedia:Reference desk/Archives/Computing/2023 December 6

From Wikipedia, the free encyclopedia
Computing desk
< December 5 << Nov | December | Jan >> December 7 >
Welcome to the Wikipedia Computing Reference Desk Archives
The page you are currently viewing is a transcluded archive page. While you can leave answers for any questions shown below, please ask new questions on one of the current reference desk pages.


December 6

[edit]

Excel Searching Question

[edit]

I have a table in Excel that has an ID column (where ID is unique) and a Date column (where Data is not necessarily unique). I then separately have a list of a subset of the IDs in the table (all IDs in this subset are unique). What is the best way to automate extracting the dates from the table that correspond to the IDs in the subset list? Thanks 147.161.237.22 (talk) 15:05, 6 December 2023 (UTC)[reply]

If I understand you correctly, it seems like VLOOKUP is the function you need. If you're using Microsoft 365, the new XLOOKUP would also be an option. There are many tutorials online, but the basic format is: =VLOOKUP(<the ID you're looking up> , <the data range holding the IDs and dates> , <the number of columns over to the right the date is from the ID>, and the word "false" without quotes marks, to prevent approximate matches). Matt Deres (talk) 15:44, 6 December 2023 (UTC)[reply]
This basic question keeps repeating. The answer is repeatedly the same. Insert a pivot table. In this case, you want the ID to be the group. You want the date to be the value. The function is concatenate. The pivot table will show each ID once along with every date per ID. I feel that the person asking this question is simply refusing to accept that a pivot table is the tool in Excel that is used to get the minimum value per ID, or the maximum value per ID, or the count of values per ID, or a list of values per ID, etc... 97.82.165.112 (talk) 17:24, 6 December 2023 (UTC)[reply]
No, a pivot table won't work here; I'm not sure where you're getting that. They want to find the dates that correspond to the IDs on the second list. For that you need a lookup function. Matt Deres (talk) 18:52, 7 December 2023 (UTC)[reply]