Issues Transferring Inventory Data

**Ricknik15** · 03-04-2015, 09:23 PM

I have an inventory table listing equipment for different areas. I also have Control Logs for each area that pulls that areas designated equipment from the Inventory Table. Each area has its own set of equipment (i.e. Area 1 has 1-A, 1-B, 1-C...; Area 2 has 2-A, 2-B, 2-C...) but certain equipment is either missing (designated by a blank cell) or is damaged (designated by an X). How can I get the Area Control Logs to list all the available equipment in a column while skipping over the blanks and the X's in a single column?
Right now, the Area Control Logs show the inventory list as:
1-A
1-B
X
1-D

1-F

I would like it to show on the Area Control Logs like this:
1-A
1-B
1-D
1-F

**FDibbins** · 03-04-2015, 09:31 PM

Hi, welcome to the forum

If your data looks something like this...

	A	B
1	1-A	1-A
2	1-B	1-B
3	X	X
4	1-D	1-D
5		1-F
6	1-F

B1=IFERROR(INDEX($A$1:$A$6,SMALL(IF(NOT(ISBLANK($A$1:$A$6)),ROW($A$1:$A$6)),ROWS($A$1:A1))),"")
If your data starts in row 2, then adjust ROW($A$1:$A$6) to ROW($A$1:$A$6)-1 etc
This is an ARRAY formula ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

**Tony Valko** · 03-04-2015, 10:33 PM

Assuming the data of interest always starts with a number.

Data Range

	A	B
1	Area	Area
2	1-A	1-A
3	1-B	1-B
4	X	1-D
5	1-D	1-F
6
7	1-F

This array formula** entered in B2:

=IFERROR(INDEX(A:A,SMALL(IF(ISNUMBER(-LEFT(A$2:A$7)),ROW(A$2:A$7)),ROWS(B$2:B2))),"")

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