Populate table using cross-referenced data

[macgyver1985]

Hello,

I really hope someone can help me or get me going in the right direction. I have a large project that I am working on but I do not imagine that it is all that difficult, I've just hit a brick wall in my thinking. The project is this: I have a spreadsheet of data provided to me containing information such as name, course number, and date completed, among others. I have a spreadsheet set up that have the names and course number already set up as a table with Name vs Course number and what I need is the date completed part filled in automatically, either by VBA, macro, or equation. The problem for what I can see boils down to essentially this:

How can I get a table of data like this:

[TABLE="width: 235"]
<TBODY>[TR]
[TD]Cookie</SPAN>
[/TD]
[TD]Month</SPAN>
[/TD]
[TD]Revenue</SPAN>
[/TD]
[/TR]
[TR]
[TD]Chocolate Chip</SPAN>
[/TD]
[TD]January</SPAN>
[/TD]
[TD="align: right"]2047</SPAN>
[/TD]
[/TR]
[TR]
[TD]Chocolate Chip</SPAN>
[/TD]
[TD]February</SPAN>
[/TD]
[TD="align: right"]1987</SPAN>
[/TD]
[/TR]
[TR]
[TD]Chocolate Chip</SPAN>
[/TD]
[TD]March</SPAN>
[/TD]
[TD="align: right"]1999</SPAN>
[/TD]
[/TR]
[TR]
[TD]Oatmeal</SPAN>
[/TD]
[TD]January</SPAN>
[/TD]
[TD="align: right"]1250</SPAN>
[/TD]
[/TR]
[TR]
[TD]Oatmeal</SPAN>
[/TD]
[TD]February</SPAN>
[/TD]
[TD="align: right"]1345</SPAN>
[/TD]
[/TR]
[TR]
[TD]Oatmeal</SPAN>
[/TD]
[TD]March</SPAN>
[/TD]
[TD="align: right"]1287</SPAN>
[/TD]
[/TR]
[TR]
[TD]Peanut Butter</SPAN>
[/TD]
[TD]January</SPAN>
[/TD]
[TD="align: right"]1292</SPAN>
[/TD]
[/TR]
[TR]
[TD]Peanut Butter</SPAN>
[/TD]
[TD]February</SPAN>
[/TD]
[TD="align: right"]1156</SPAN>
[/TD]
[/TR]
[TR]
[TD]Peanut Butter</SPAN>
[/TD]
[TD]March</SPAN>
[/TD]
[TD="align: right"]1208</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]


To look like this:

[TABLE="width: 291"]
<TBODY>[TR]
[TD][/TD]
[TD]January</SPAN>
[/TD]
[TD]February</SPAN>
[/TD]
[TD]March</SPAN>
[/TD]
[/TR]
[TR]
[TD]Chocolate Chip</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Oatmeal</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Peanut Butter</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]


With the sales revenue automatically being filled in? I have tried using VLOOKUP function with an imbeded match function, but that seems to work best performing the reverse of what I want. Also I could only get it to work on the first row. The above is just a simple example, my actual data is quite comprehensive, containing thousands of rows and many columns, and the tables of sorted data will be spread across several tabs.

Can anyone help me or at least get me going in the right direction?

Thank you,

MacGyver

 

[Weazel]

I think a pivot table would be a good way to go with Month in the column labels, Cookie in the Row label, and Revenue in the Values

Excel 2010

	A	B	C	D
Sum of Revenue	Month
Cookie	January	February	March
Chocolate Chip
Oatmeal
Peanut Butter

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]2047[/TD]
[TD="align: right"]1987[/TD]
[TD="align: right"]1999[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]1250[/TD]
[TD="align: right"]1345[/TD]
[TD="align: right"]1287[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]1292[/TD]
[TD="align: right"]1156[/TD]
[TD="align: right"]1208[/TD]

</tbody>

Sheet11​

 

[Weazel]

I'm still in favor of a pivot table but if you really want formulas you could try these ....

I only posted the first formula for each data type...

you should just need to drag them down and right to get your unique list of cookies and months and over and down on the sumifs for the revenue.

Could probably add an iferror in it for the N/A's too

Hopefully that gets you going in the direction you are looking for


Excel 2010

	E	F

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFF00"]January[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FFFF00"]Chocolate Chip[/TD]
[TD="align: right"]2047[/TD]

</tbody>

Sheet10​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]F2[/TH]
[TD="align: left"]=SUMIFS($C$2:$C$10,$A$2:$A$10,$E2,$B$2:$B$10,F$1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]F1[/TH]
[TD="align: left"]{=INDEX($B$2:$B$10,MATCH(0,COUNTIF($E$1:E1,$B$2:$B$10),0))}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E2[/TH]
[TD="align: left"]{=INDEX($A$2:$A$10,MATCH(0,COUNTIF($E$1:E1,$A$2:$A$10),0))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[macgyver1985]

Weazel,

Thanks for the tip about the pivot tables, I have never really used them. However I have one major problem. The mock data above mistakenly does not represent the data I am actually using. I honestly didn't see much of a difference until I tried the table. My actual data is more like this:

User Course Title Date of Completion
Smith, John Course 1 1/1/2013 12:00
Smith, John Course 2 1/6/2013 14:00
Doe, Jane Course 1 3/3/2013 6:00
Doe, Jane Course 2 3/8/2013 10:00

The pivot table works only if I wanted to know if it was completed, not when. Is there a way to get the pivot table to fill in the date so I get the below table, with the column titles as the course title and the row titles as the user?

Course Title ---->
User
|
|
V

Thank you for your help,

MacGyver

 

[Weazel]

not sure if this is what you're trying to achieve but with User for row labels, Course title for Column Labels and Date of Completion in Values


I removed the times so it would fit better here but they would still be in the cell.

[TABLE="width: 376"]
<TBODY>[TR]
[TD]Sum of Date of Completion[/TD]
[TD]Course Title[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]User[/TD]
[TD]Course 1[/TD]
[TD]Course 2[/TD]
[/TR]
[TR]
[TD]Doe,Jane[/TD]
[TD="align: right"]3/3/2013[/TD]
[TD="align: right"]3/8/2013[/TD]
[/TR]
[TR]
[TD]Smith,John[/TD]
[TD="align: right"]1/1/2012[/TD]
[TD="align: right"]1/6/2013[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>[/TABLE]

 

[macgyver1985]

Hello,

For my purposes the SUMIFS function actually worked the best. Essentially, I need to create a spreadsheet that is set up so all the user has to do is paste in the data (it always has the same columns) and all the set up tabs and tables are automatically filled. So in other words this spreadsheet needs to be really easy for non-excel people to use and see data. For this purpose using an equation was by far and away the best option. While a pivot table orientates the data quicker and gives no headache it does not lend itself to people who barely use excel. That being said, I've had another curve ball thrown my way. There are duplicates in the data, so when a person completes a course and they need to refresh it the following year both completion dates show up, and SUMIFS does exactly what it should and adds up the dates. My current equation is this: (for John Doe and Course 1) =SUMIFS(Date,UserID,A15,Course,C15) Which works perfectly if there are no duplicates. Is there a way to imbed a second function in SUMIFS to tell it to use the maximum date or is there a function that I can use that will function similarly to SUMIFS that will just display the max date or most recent?

Thank you for your help.

[TABLE="class: grid, width: 681, align: center"]
<TBODY>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]

User ID</SPAN>​

[/TD]
[TD]

User Last Name</SPAN>​

[/TD]
[TD]

User First Name</SPAN>​

[/TD]
[TD]

Course</SPAN>​

[/TD]
[TD]

Status</SPAN>​

[/TD]
[TD]

Course Code</SPAN>​

[/TD]
[TD]

Date</SPAN>​

[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]

A1004</SPAN>​

[/TD]
[TD]

Doe</SPAN>​

[/TD]
[TD]

John</SPAN>​

[/TD]
[TD]

Course 1</SPAN>​

[/TD]
[TD]

Active</SPAN>​

[/TD]
[TD]

CR-1</SPAN>​

[/TD]
[TD]

1/24/2013</SPAN>​

[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]

A1004</SPAN>​

[/TD]
[TD]

Doe</SPAN>​

[/TD]
[TD]

John</SPAN>​

[/TD]
[TD]

Course 2</SPAN>​

[/TD]
[TD]

Active</SPAN>​

[/TD]
[TD]

CR-2</SPAN>​

[/TD]
[TD]

3/21/2013</SPAN>​

[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]

A2008</SPAN>​

[/TD]
[TD]

Doe</SPAN>​

[/TD]
[TD]

Jane</SPAN>​

[/TD]
[TD]

Course 1</SPAN>​

[/TD]
[TD]

Active</SPAN>​

[/TD]
[TD]

CR-1</SPAN>​

[/TD]
[TD]

1/24/2013</SPAN>​

[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]

A2008</SPAN>​

[/TD]
[TD]

Doe</SPAN>​

[/TD]
[TD]

Jane</SPAN>​

[/TD]
[TD]

Course 3</SPAN>​

[/TD]
[TD]

Active</SPAN>​

[/TD]
[TD]

CR-3</SPAN>​

[/TD]
[TD]

1/8/2013</SPAN>​

[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]

B3165</SPAN>​

[/TD]
[TD]

Cooper</SPAN>​

[/TD]
[TD]

William</SPAN>​

[/TD]
[TD]

Course 4</SPAN>​

[/TD]
[TD]

Active</SPAN>​

[/TD]
[TD]

CR-4</SPAN>​

[/TD]
[TD]

5/7/2013</SPAN>​

[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]

B3165</SPAN>​

[/TD]
[TD]

Cooper</SPAN>​

[/TD]
[TD]

William</SPAN>​

[/TD]
[TD]

Course 3</SPAN>​

[/TD]
[TD]

Active</SPAN>​

[/TD]
[TD]

CR-3</SPAN>​

[/TD]
[TD]

5/23/2013</SPAN>​

[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]

A1004</SPAN>​

[/TD]
[TD]

Doe</SPAN>​

[/TD]
[TD]

John</SPAN>​

[/TD]
[TD]

Course 1</SPAN>​

[/TD]
[TD]

Active</SPAN>​

[/TD]
[TD]

CR-1</SPAN>​

[/TD]
[TD]

1/5/2012</SPAN>​

[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]

A2008</SPAN>​

[/TD]
[TD]

Doe</SPAN>​

[/TD]
[TD]

Jane</SPAN>​

[/TD]
[TD]

Course 3</SPAN>​

[/TD]
[TD]

Active</SPAN>​

[/TD]
[TD]

CR-3</SPAN>​

[/TD]
[TD]

2/5/2011</SPAN>​

[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]

B3165</SPAN>​

[/TD]
[TD]

Cooper</SPAN>​

[/TD]
[TD]

William</SPAN>​

[/TD]
[TD]

Course 3</SPAN>​

[/TD]
[TD]

Active</SPAN>​

[/TD]
[TD]

CR-3</SPAN>​

[/TD]
[TD]

5/6/2010</SPAN>​

[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]

B3165</SPAN>​

[/TD]
[TD]

Cooper</SPAN>​

[/TD]
[TD]

William</SPAN>​

[/TD]
[TD]

Course 3</SPAN>​

[/TD]
[TD]

Active</SPAN>​

[/TD]
[TD]

CR-3</SPAN>​

[/TD]
[TD]

5/20/2012</SPAN>​

[/TD]
[/TR]
</TBODY>[/TABLE]

[TABLE="class: grid, width: 587"]
<TBODY>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD]User ID
[/TD]
[TD]Name
[/TD]
[TD]Course 1</SPAN>
[/TD]
[TD]Course 2</SPAN>
[/TD]
[TD]Course 3</SPAN>
[/TD]
[TD]Course 4</SPAN>
[/TD]
[/TR]
[TR]
[TD]15
[/TD]
[TD]A1004</SPAN>
[/TD]
[TD]Doe, John</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16
[/TD]
[TD]A2008</SPAN>
[/TD]
[TD]Doe, Jane</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17
[/TD]
[TD]B3165</SPAN>
[/TD]
[TD]Cooper, William</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

 

[Weazel]

maybe something like this....

I used the large to force an error if the course and userid didn't match, then used to iferror for the blank. The max formula returns a zero if the criteria isn't met and because of the date formatting will return 1/0/1900 so it looks a bit confusing.
make sure you enter it with Ctrl+Shift+Enter as its an array then drag it right and down.


Excel 2010

	A	B	C	D	E	F

<tbody>
[TD="align: center"]14[/TD]
[TD="bgcolor: #FAFAFA"]User ID[/TD]
[TD="bgcolor: #FAFAFA"]Name[/TD]
[TD="bgcolor: #FAFAFA"]Course 1[/TD]
[TD="bgcolor: #FAFAFA"]Course 2[/TD]
[TD="bgcolor: #FAFAFA"]Course 3[/TD]
[TD="bgcolor: #FAFAFA"]Course 4[/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: #FAFAFA"]A1004[/TD]
[TD="bgcolor: #FAFAFA"]Doe, John[/TD]
[TD="align: right"]1/24/2013[/TD]
[TD="align: right"]3/21/2013[/TD]

[TD="align: center"]16[/TD]
[TD="bgcolor: #FAFAFA"]A2008[/TD]
[TD="bgcolor: #FAFAFA"]Doe, Jane[/TD]
[TD="align: right"]1/24/2013[/TD]

[TD="align: right"]1/8/2013[/TD]

[TD="align: center"]17[/TD]
[TD="bgcolor: #FAFAFA"]B3165[/TD]
[TD="bgcolor: #FAFAFA"]Cooper, William[/TD]

[TD="align: right"]5/23/2013[/TD]
[TD="align: right"]5/7/2013[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C15[/TH]
[TD="align: left"]{=IFERROR(LARGE(IF(($A$2:$A$11=$A15)*($D$2:$D$11=C$14),$G$2:$G$11),1),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[macgyver1985]

Thank you very much for your help. My project is now running smoothly and I have learned a great deal about Excel that I either did not know or forgot.