# Should be a simple task :-/

#### ahab1981

##### New Member
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

### Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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?

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

i wonder if something like vlookup could be used?

Excel 2010
AB
1table 1
2item 26
3item 57
4
5
6
7Table 2
8item 15
9item 26
10item 33
11item 45
12item 57
13item 61
14item 72
15item 83
16
17
18
Sheet1
Cell Formulas
RangeFormula
B2=VLOOKUP(A2,\$A\$8:\$B\$25,2,FALSE)

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

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:

Code:
``=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):

Code:
``=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:

Code:
``=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):

Code:
``=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?

Last edited:
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
AB
1table 1
2item 31
3item 52
4item 73
5item 84
6item 126
7#N/A#N/A
8
9
10
11
12
13
14Table 2
15item 10
16item 20
17item 31
18item 40
19item 52
20item 60
21item 73
22item 84
23item 90
24item 100
25item 110
26item 126
27
28
29
30
31
Sheet1
Cell Formulas
RangeFormula
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.

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"

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.

Replies
17
Views
808
Replies
2
Views
4K
Replies
1
Views
88
Replies
1
Views
153
Replies
4
Views
136

1,196,487
Messages
6,015,482
Members
441,898
Latest member
kofafa

### 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.

### Which adblocker are you using?

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

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