Adding extra lines within a spreadsheet of new product codes

[Irenekennedy]

I have a spreadsheet that has 2 tabs one that shows products with locations and a tab with empty locations

I want to join them together to see on one spreadsheet

I have attached my spreadsheet to help

[alansidman]

I want to join them together to see on one spreadsheet

Please explain what you mean by this. How do you want them joined together? Do you wish the empty to be added at the bottom of your list? Why not just cut and paste? Am I missing something here?

[Irenekennedy]

I want to add the empty locations into the locations with products so I can see on one spreadsheet all locations with and without products

[Glenn Kennedy]

Here is one wat, using two array formulae. I suspect that cut and paste might be easier though. However, if this is really what you want to do, then the addition of a couple of Named Ranges would make this HUGELY easier to manage. Do you know how to do that?

Formula for the bins, copy down:

=IFERROR(IFERROR(INDEX(bin1!C:C,SMALL(IF(bin1!$C$2:$C$271<>"",ROW(bin1!$C$2:$C$271)),ROWS($1:1))),INDEX(Empty!$A:$A,SMALL(IF(Empty!$A$2:$A$748<>"",ROW(Empty!$A$2:$A$748)),ROWS($1:1)-(COUNTA(bin1!C:C)-1)))),"")

for the other bits, copy across and down:

=IFERROR(INDEX(bin1!A:A,SMALL(IF(bin1!$C$2:$C$271<>"",ROW(bin1!$C$2:$C$271)),ROWS($1:1))),"")

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

[Irenekennedy]

sorry I must have not explained myself correctly

I have a spreadsheet tab that shows

Material code - description and bin location (B01S001X01) I have another spreadsheet that has bin locations with no products that I want to add to the first tab with info so the spreadsheet would look like below where the empty location would be blank

Material Desciption Stor. Bin
822 H/Piece Eng Labour (per 1/4hr) B01S001X01
B01S001X02

[Glenn Kennedy]

How do you want duplicates handled. There are a few of these in your dataset:

B01S033X02
B01S060X03
B01S001X01
B01S059X06
B01S078X04
B01S051X08
B01S020X01
B01S041X02
B01S029X01
B01S027X01
B01S059X07

[Glenn Kennedy]

I have just noticed that you attached an xls file (97-2003), but your profile indicates that you are using Excel 2010. Which Excel version is this sheet needed for? The attachment above will NOT work, without modification, in Excel 2003 or earlier.

[Glenn Kennedy]

OK. So you want the lists MERGED and SORTED? Correct. Again, though - which version of Excel is this wanted for?

[Irenekennedy]

I have both versions of excel xls and xlsx either would be great so I can understand

[alansidman]

The quickest way to do this in my opinion. Copy column A on the tab containing the empty bins. Paste into the bottom of column C of the other tab. Now click on cell A2, and on the ribbon click on the Data tab. Select Sort and indicate that you have headers, and sort column C.