Create a list of items based on another list

[jeremy9400]

Hi, i have a price list with all with items running down column A, C, E, and G, and the quantity running down in columns B, D, F, and H, what i want is when a quantity is entered for a item it should copy that item in a new column, so far i was only able to do so for one column but i cant do it for all columns together, i would prefer not having to do it with VBA, any idea what to do???

with this formulas i get it to work for one column

=IF(ROWS($A$2:A2)>COUNTA($B$2:$B$5),"",INDEX($A$2:$A$5,SMALL(IF($B$2:$B$5<>"",ROW($A$2:$A$5)-ROW($A$2)+1),ROWS($A$5:A5))))

=INDEX($A$2:$C$11, MATCH(0, COUNTIF($E$7:$E7, $A$2:$A$11)+($B$2:$B$11<>$F$1), 0), COLUMN(A1))

[mikeTRON]

I would use a pivot table and a filter on that pivot table, as that is less work IMO.

[Tony Valko]

The simple solution is to put all the items in 1 column and all the qtys in 1 column.

[jeremy9400]

but i have it across four columns so everything is in size, otherwise you would have to scroll a lot

[Tony Valko]

Does the new combined list ever get so long that you have to scroll?

It may be possible to do what you want but the resulting formula will be extremely long and complex.

I also hate to have to scroll to see data but sometimes ya gota do what ya gota do!

What's that 2nd formula doing? It looks like it extracts unique entries. Does that mean you just want the unique entries from the multiple columns? If so, the degree of difficulty just went off the chart!

[jeremy9400]

can you give me the formula? if it were just for me i wouldnt mind but since i am going to have a lot of people using it so i want to be the most convenient way possible, and regarding the second formula i can set it to extract the rows which quantity is entered but that i also can only do for one column.

[Tony Valko]

Here you go...

Data Range

	A	B	C	D	E	F	G	H	I	J
1	Product	Qty	Product	Qty	Product	Qty	Product	Qty	------	All Products
2	Item1		Item6		Item11		Item16			Item2
3	Item2	10	Item7		Item12		Item17			Item5
4	Item3		Item8		Item13	5	Item18			Item10
5	Item4		Item9		Item14		Item19			Item13
6	Item5	10	Item10	10	Item15		Item20	8		Item20
7

This array formula** entered in J2:

=IFERROR(IFERROR(IFERROR(IFERROR(INDEX(A:A,SMALL(IF(B$2:B$6<>"",ROW(B$2:B$6)),ROWS(J$2:J2))),INDEX(C:C,SMALL(IF(D$2:D$6<>"",ROW(D$2:D$6)),ROWS(J$2:J2)-COUNTIF(B$2:B$6,"<>")))),INDEX(E:E,SMALL(IF(F$2:F$6<>"",ROW(F$2:F$6)),ROWS(J$2:J2)-SUM(COUNTIF(B$2:B$6,"<>"),COUNTIF(D$2:D$6,"<>"))))),INDEX(G:G,SMALL(IF(H$2:H$6<>"",ROW(H$2:H$6)),ROWS(J$2:J2)-SUM(COUNTIF(B$2:B$6,"<>"),COUNTIF(D$2:D$6,"<>"),COUNTIF(F$2:F$6,"<>"))))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

That may be the longest formula I've ever written!

[Tony Valko]

Improvement!

Since the qty will be a number we can replace all the COUNTIF functions with shorter COUNT functions.

=IFERROR(IFERROR(IFERROR(IFERROR(INDEX(A:A,SMALL(IF(B$2:B$6<>"",ROW(B$2:B$6)),ROWS(J$2:J2))),INDEX(C:C,SMALL(IF(D$2:D$6<>"",ROW(D$2:D$6)),ROWS(J$2:J2)-COUNT(B$2:B$6)))),INDEX(E:E,SMALL(IF(F$2:F$6<>"",ROW(F$2:F$6)),ROWS(J$2:J2)-COUNT(B$2:B$6,D$2:D$6)))),INDEX(G:G,SMALL(IF(H$2:H$6<>"",ROW(H$2:H$6)),ROWS(J$2:J2)-COUNT(B$2:B$6,D$2:D$6,F$2:F$6)))),"")

[jeremy9400]

WOW WOW WOW this works mega!!! thanks so much, one more question how can i add the quantity to the H column to all items in the J column?

[Tony Valko]

Like this...

Data Range

	A	B	C	D	E	F	G	H	I	J	K
1	Product	Qty	Product	Qty	Product	Qty	Product	Qty	------	All Products	Qty
2	Item1		Item6		Item11		Item16			Item2	10
3	Item2	10	Item7		Item12		Item17			Item5	10
4	Item3		Item8		Item13	5	Item18			Item10	10
5	Item4		Item9		Item14		Item19			Item13	5
6	Item5	10	Item10	10	Item15		Item20	8		Item20	8
7

This formula entered in K2 and copied down:

=IF(J2="","",SUMIF(A$2:G$6,J2,B$2:H$6))