Should be a simple task :-/

[ahab1981]

Hi, I have been asked to create a spreadsheet for colleague in spite of being a relative novice! The problem is...
I have two tables and I need for table1 to auto-populate according to table2's data. Table 2 is always the same except for one column which shows quantity. The idea will be that people can look over the long table2 and fill in what they need, this will create a shorter, more manageable table 1 (on another tab).

Unfortunately, just simply filtering table2 is not an option.

I'm sure there is an easy way to do this but having looked through forums and blogs, I can't find one. Any help really appreciated.

Cheers

Tom

 

[MarcelBeug]

What algorithm is required to auto-populate table 1?
In other words: what is in table2 that is not in table1, or: why is table 1 shorter than table2?

 

[ahab1981]

Hi Marcel, Table2 is a long list of resources. It only has the resource name and quantity, like a menu. People will go down it and fill in the quantities they need against the resources that they want. As all resources will be at a default quantity of 0, I would like table1 to only show thew the resources that now has a quantity of greater than 0 against them. Table2 is a couple of hundred deep but when people fill in the form they will only want about 5-10 different resources.

Thanks

 

[tybaltlives]

i wonder if something like vlookup could be used?

Excel 2010
	A	B
1	table 1
2	item 2	6
3	item 5	7
4
5
6
7	Table 2
8	item 1	5
9	item 2	6
10	item 3	3
11	item 4	5
12	item 5	7
13	item 6	1
14	item 7	2
15	item 8	3
16
17
18
Sheet1

Cell Formulas
Range	Formula
B2	=VLOOKUP(A2,$A$8:$B$25,2,FALSE)

 

[ahab1981]

Hi R, thanks for your help, the problem is that it isn't pulled through on a specific reference, rather table1 should automatically pull through the name and quantity of any resource that has a quantity of greater than 0. There may be a way to do it though vlookup but I haven't been able to find it! Sorry if I haven't been very clear and thanks for your effort.

Tom

 

[MarcelBeug]

Assuming both table1 and table2 are actually formatted as table, each with columns "Resource" and "Quantity".
All quantities in table2 must have a numeric value.

First you will need a helper field for the number of records in Table2 > 0, let's say $D$1 with formula:

=COUNTIF(Table2[Quantity],">0")

In the "Resource" field of Table1, on the first data row (assuming this is A2), enter the following array formula (confirm with CTRL-SHIFT-ENTER, not just ENTER):

=IF(ROW(A2)-ROW(A$2)+1>$D$1,"",INDEX(Table2[Resource];SMALL(IF(Table2[Quantity]>0,ROW(INDIRECT("1:"&COUNT(Table2[Quantity])))),ROW(A2)-ROW(A$2)+1)))

If the resources in table2 are all unique,you can auto-populate quantities using VLOOKUP.
In B2 enter:

=IF(ROW(A2)-ROW(A$2)+1>$D$1,"",VLOOKUP(([@Resource],Table2,2,0))

If resources in table2 are not unique, you need a similar formula as the one in the resource column.
In B2, enter the following array formula (confirm with CTRL-SHIFT-ENTER, not just ENTER):

=IF(ROW(B2)-ROW(B$2)+1>$D$1,"",INDEX(Table2[Quantity],SMALL(IF(Table2[Quantity]>0,ROW(INDIRECT("1:"&COUNT(Table2[Quantity])))),ROW(B2)-ROW(B$2)+1)))

Is this simple or not?

 

[tybaltlives]

OK. Here is another way. All formulas are array formulas (ctrl-shft-ent). The formulas in row 2 get you started by identifying the first non-zero value. After that I use the indirect formula to build the result. Note that since the values in table 2 start on line 15, the number 15 is added to some of the internal formula results to enable the search in the proper place. Copy the formulast down until you get a #N/A - then there are no more non zero values.

Excel 2010
	A	B
1	table 1
2	item 3	1
3	item 5	2
4	item 7	3
5	item 8	4
6	item 12	6
7	#N/A	#N/A
8
9
10
11
12
13
14	Table 2
15	item 1	0
16	item 2	0
17	item 3	1
18	item 4	0
19	item 5	2
20	item 6	0
21	item 7	3
22	item 8	4
23	item 9	0
24	item 10	0
25	item 11	0
26	item 12	6
27
28
29
30
31
Sheet1

Cell Formulas
Range	Formula
B2	=VLOOKUP(A2,$A$15:$B$31,2,FALSE)
B3	=VLOOKUP(A3,$A$15:$B$31,2,FALSE)
B4	=VLOOKUP(A4,$A$15:$B$31,2,FALSE)
B5	=VLOOKUP(A5,$A$15:$B$31,2,FALSE)
B6	=VLOOKUP(A6,$A$15:$B$31,2,FALSE)
B7	=VLOOKUP(A7,$A$15:$B$31,2,FALSE)
A2	{=INDEX(A15:A30,MATCH(1,--(B15:B30<>0),0))}
A3	{=INDEX(INDIRECT("A"&MATCH(A2,$A$15:$A$31,0)+15&":A30"),MATCH(1,--(INDIRECT("B"&MATCH(A2,$A$15:$A$31,0)+15&":B30")<>0),0))}
A4	{=INDEX(INDIRECT("A"&MATCH(A3,$A$15:$A$31,0)+15&":A30"),MATCH(1,--(INDIRECT("B"&MATCH(A3,$A$15:$A$31,0)+15&":B30")<>0),0))}
A5	{=INDEX(INDIRECT("A"&MATCH(A4,$A$15:$A$31,0)+15&":A30"),MATCH(1,--(INDIRECT("B"&MATCH(A4,$A$15:$A$31,0)+15&":B30")<>0),0))}
A6	{=INDEX(INDIRECT("A"&MATCH(A5,$A$15:$A$31,0)+15&":A30"),MATCH(1,--(INDIRECT("B"&MATCH(A5,$A$15:$A$31,0)+15&":B30")<>0),0))}
A7	{=INDEX(INDIRECT("A"&MATCH(A6,$A$15:$A$31,0)+15&":A30"),MATCH(1,--(INDIRECT("B"&MATCH(A6,$A$15:$A$31,0)+15&":B30")<>0),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[ahab1981]

Guys, really, really appreciate you both taking your time to help me out here. I'm sure they are both correct and am going to enjoy trying to apply them now!!! Which will certainly be a test for my "skills"

 

[SpillerBD]

What? No suggested Pivot Tables!
While a Pivot Table is usually used to Sum/ Average/Count/.... etc. a pivot Table can easily do what is requested.
It may be immediately beyond a novice, but is something that could be used. Could allow Ahab to wow his managers too!
Sign up for MSFT Learning. https://onlinelearning.microsoft.com/?whr=default
https://support.office.com/en-us/article/PivotTable-I-Get-started-with-PivotTable-reports-in-Excel-2007-bfe774d3-3e20-46f7-b6a3-f7984855d665

 

[tybaltlives]

true. pivot tables are extremely powerful and would obtain this solution in a snap. one reason I sometimes look for the equation solution is that if the original data changes the pivot table needs to be manually refreshed or the input range changed. If I am making a spreadsheet for someone else in the organization they sometimes forget to do the refresh. Heck, I forget too. So sometimes the equations are a better way to automate the output. But not always. Pivot tables should certainly be considered as an option.