CODEWITHSUNDEEP
excel
anjaryes
anjaryes

Reputation: 111

How To Get Cell Value into Matrix Table in Excel

I am totally new of using advance for data processing in excel. So, I hope anyone here willing to help with this problem.

I have a data table like the table below, which is consist of ID that refer to a Name. Each ID is having a Date value, and grouped by group number in the first column of table.

>     GROUP ID  NAME    DATE
>     223   4   DEXY    02/03/2019
>     223   2   CLARKE  03/04/2019
>     223   3   ANDY    09/09/2018
>     223   1   DAVE    07/06/2019
>     224   1   DAVE    01/08/2019
>     224   2   CLARKE  03/03/2014
>     224   4   DEXY    04/05/2019
>     224   3   ANDY    08/06/2019
>     225   4   DEXY    01/05/2019
>     225   3   ANDY    03/03/2014
>     225   1   DAVE    12/08/2019
>     225   2   CLARKE  18/10/2019
>     226   1   DAVE    24/12/2019
>     226   2   CLARKE  29/02/2020
>     226   4   DEXY    06/05/2020

enter image description here

What I wanna do is automatically get the value of Date based on Group Number and ID number that placed horizontally. I have tried using vlookup but I have no idea how to use it properly in this case. My final table has to be like this.

enter image description here

In my real data, i have to process 2399 of groups number like that. I will really appreciate if anyone could help me solve this problem, Thank you.

Upvotes: 0

Views: 252

Answers (1)

Jbowman
Jbowman

Reputation: 480

Is this the output your looking for?

enter image description here

This is a pivot table (row=Group, Column=ID, value =SUM(Date) -- remember to format as a date), no totals.

I noticed the date format is different. when I corrected for the dates, my output became:

enter image description here

The assumption here, though, is that each group has one and only one of the expected id's.

Here is a sample with a group, id's, and names, but no dates:

enter image description here

Upvotes: 2

Related Questions