Populate table using cross-referenced data

macgyver1985

New Member
Joined
May 31, 2013
Messages
4
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:

Cookie</SPAN>
Month</SPAN>
Revenue</SPAN>
Chocolate Chip</SPAN>
January</SPAN>
2047</SPAN>
Chocolate Chip</SPAN>
February</SPAN>
1987</SPAN>
Chocolate Chip</SPAN>
March</SPAN>
1999</SPAN>
Oatmeal</SPAN>
January</SPAN>
1250</SPAN>
Oatmeal</SPAN>
February</SPAN>
1345</SPAN>
Oatmeal</SPAN>
March</SPAN>
1287</SPAN>
Peanut Butter</SPAN>
January</SPAN>
1292</SPAN>
Peanut Butter</SPAN>
February</SPAN>
1156</SPAN>
Peanut Butter</SPAN>
March</SPAN>
1208</SPAN>

<TBODY>
</TBODY>


To look like this:

January</SPAN>
February</SPAN>
March</SPAN>
Chocolate Chip</SPAN>
Oatmeal</SPAN>
Peanut Butter</SPAN>

<TBODY>
</TBODY>


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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Weazel

Well-known Member
Joined
Dec 24, 2011
Messages
3,155
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
ABCD
3Sum of RevenueMonth
4CookieJanuaryFebruaryMarch
5Chocolate Chip204719871999
6Oatmeal125013451287
7Peanut Butter129211561208

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet11
 

Weazel

Well-known Member
Joined
Dec 24, 2011
Messages
3,155
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
EF
1January
2Chocolate Chip2047

<tbody>
</tbody>
Sheet10

Worksheet Formulas
CellFormula
F2=SUMIFS($C$2:$C$10,$A$2:$A$10,$E2,$B$2:$B$10,F$1)

<tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
F1{=INDEX($B$2:$B$10,MATCH(0,COUNTIF($E$1:E1,$B$2:$B$10),0))}
E2{=INDEX($A$2:$A$10,MATCH(0,COUNTIF($E$1:E1,$A$2:$A$10),0))}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
 

macgyver1985

New Member
Joined
May 31, 2013
Messages
4
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

Well-known Member
Joined
Dec 24, 2011
Messages
3,155

ADVERTISEMENT

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.

Sum of Date of CompletionCourse Title
UserCourse 1Course 2
Doe,Jane3/3/20133/8/2013
Smith,John1/1/20121/6/2013

<TBODY>
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>
 

macgyver1985

New Member
Joined
May 31, 2013
Messages
4
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.

A
B
C
D
E
F
G
1

User ID</SPAN>

User Last Name</SPAN>

User First Name</SPAN>

Course</SPAN>

Status</SPAN>

Course Code</SPAN>

Date</SPAN>
2

A1004</SPAN>​

Doe</SPAN>​

John</SPAN>​

Course 1</SPAN>​

Active</SPAN>​

CR-1</SPAN>​

1/24/2013</SPAN>​
3

A1004</SPAN>​

Doe</SPAN>​

John</SPAN>​

Course 2</SPAN>​

Active</SPAN>​

CR-2</SPAN>​

3/21/2013</SPAN>​
4

A2008</SPAN>​

Doe</SPAN>​

Jane</SPAN>​

Course 1</SPAN>​

Active</SPAN>​

CR-1</SPAN>​

1/24/2013</SPAN>​
5

A2008</SPAN>​

Doe</SPAN>​

Jane</SPAN>​

Course 3</SPAN>​

Active</SPAN>​

CR-3</SPAN>​

1/8/2013</SPAN>​
6

B3165</SPAN>​

Cooper</SPAN>​

William</SPAN>​

Course 4</SPAN>​

Active</SPAN>​

CR-4</SPAN>​

5/7/2013</SPAN>​
7

B3165</SPAN>​

Cooper</SPAN>​

William</SPAN>​

Course 3</SPAN>​

Active</SPAN>​

CR-3</SPAN>​

5/23/2013</SPAN>​
8

A1004</SPAN>​

Doe</SPAN>​

John</SPAN>​

Course 1</SPAN>​

Active</SPAN>​

CR-1</SPAN>​

1/5/2012</SPAN>​
9

A2008</SPAN>​

Doe</SPAN>​

Jane</SPAN>​

Course 3</SPAN>​

Active</SPAN>​

CR-3</SPAN>​

2/5/2011</SPAN>​
10

B3165</SPAN>​

Cooper</SPAN>​

William</SPAN>​

Course 3</SPAN>​

Active</SPAN>​

CR-3</SPAN>​

5/6/2010</SPAN>​
11

B3165</SPAN>​

Cooper</SPAN>​

William</SPAN>​

Course 3</SPAN>​

Active</SPAN>​

CR-3</SPAN>​

5/20/2012</SPAN>​

<TBODY>
</TBODY>

A
B
C
D
E
F
14
User ID
Name
Course 1</SPAN>
Course 2</SPAN>
Course 3</SPAN>
Course 4</SPAN>
15
A1004</SPAN>
Doe, John</SPAN>
16
A2008</SPAN>
Doe, Jane</SPAN>
17
B3165</SPAN>
Cooper, William</SPAN>

<TBODY>
</TBODY>
 

Weazel

Well-known Member
Joined
Dec 24, 2011
Messages
3,155
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
ABCDEF
14User IDNameCourse 1Course 2Course 3Course 4
15A1004Doe, John1/24/20133/21/2013
16A2008Doe, Jane1/24/20131/8/2013
17B3165Cooper, William5/23/20135/7/2013

<tbody>
</tbody>
Sheet1

Array Formulas
CellFormula
C15{=IFERROR(LARGE(IF(($A$2:$A$11=$A15)*($D$2:$D$11=C$14),$G$2:$G$11),1),"")}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
 

macgyver1985

New Member
Joined
May 31, 2013
Messages
4
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,480
Messages
5,770,337
Members
425,612
Latest member
martinijr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top