autocopy applicable data from one sheet to another in excel

**Onesie** · 02-11-2009, 01:32 PM

I need to automatically move rows on one sheet in excel to another sheet, given that the Selected column is blank. Meaning the second sheet will only contain those not selected from the first sheet. I have attached a sample of data. The first tab is Before, second is After. In the sample data I want the records of Carrie and Mark to be copied to sheet 2 "After" since they were not selected. thanks for any help on this.

**JBeaucaire** · 02-11-2009, 02:03 PM

These are array formulas. If you need more range to the list, copy the last cell in a column down as far as needed, then highlight all the cells from row2 down, press F2, then CTRL-SHIFT-ENTER to reset the array to the new length.

If you edit the formula in a column to extend it, be sure to press CTRL-SHIFT-ENTER to complete. Each column indexes a different column from the first sheet.

This a dynamic list. It will add and subtract items in realtime as you add/subtract the X on the first sheet.

**Onesie** · 02-11-2009, 05:27 PM

This is awesome!! Only one problem, I need the second sheet to display items that were not selected. I tried to change it, but that messed things up. Can you help change it so it shows items that are not selected on the after tab?

**JBeaucaire** · 02-11-2009, 05:34 PM

Yes, but if you do this it's going to "Match" all the extra empty rows, too, and fill your chart with zeros.

I turned off Show Zero Values in the Tools > Options > General for the sheet After to suppress all the zeros that came over, too.

**Onesie** · 02-12-2009, 01:57 PM

soo...i tried to copy your example to my actual data and I've been struggling. So i've added the actual number of columns that I would need to copy and I need the same array. Same thing, this time I need the range to include the entire worksheet, there might be as many as 40,000 entries on the sheet that needs to be copied. Thanks again for your help.

**JBeaucaire** · 02-12-2009, 02:35 PM

Um, 40,000 entries? This array function will be in how many cells on the AFTER sheet? This could slow your sheet to a crawl.

I'll make the adjustment, but if it snails down like I fear, you may need to go to the Programming forum and ask for an "on demand" macro to do the transfer. The down side is you have to ask for it to run, the upside is your sheet will stay peppy, fast.

**JBeaucaire** · 02-12-2009, 03:04 PM

If I put this array in one column only, and had it search through only 2000 rows of Before, and it's slow. If I change the array to 40,000 rows the sheet never recovers trying to calculate. That's too much data, and that was only in 20 cells on the After sheet.

This won't work for that much data.