Need help Taking alot data from one sheet (if not blank) and copying toa list on another sheet.

[Alex Zuniga]

I Am trying to take information from one Sheet where for example cells F8=(blankcell) F10=Break F12=(blankcell) J8=Cleaning J10=waiting and have it automatically copied to another sheet where the cells that are not blank have their contents listed without blanks and without repeating cells. for example
Sheet2
Break
Cleaning
Waiting

So far i tried using huge if statements in the destination cells to try to locate the cells with information in them

=IF(LEN('PPI DATA'!F8)>0,'PPI DATA'!F8,IF(LEN('PPI DATA'!F10)>0,'PPI DATA'!F10,IF(LEN('PPI DATA'!F12)>0,'PPI DATA'!F12,IF(LEN('PPI DATA'!J8)>0,'PPI DATA'!J8,IF(LEN('PPI DATA'!J10)>0,'PPI DATA'!J10,IF(LEN('PPI DATA'!J12)>0,'PPI DATA'!J12,IF(LEN('PPI DATA'!N8)>0,'PPI DATA'!N8,IF(LEN('PPI DATA'!N10)>0,'PPI DATA'!N10,IF(LEN('PPI DATA'!N12)>0,'PPI DATA'!N12,IF(LEN('PPI DATA'!R8)>0,'PPI DATA'!R8,IF(LEN('PPI DATA'!R10)>0,'PPI DATA'!R10,IF(LEN('PPI DATA'!R12)>0,'PPI DATA'!R12,IF(LEN('PPI DATA'!V8)>0,'PPI DATA'!V8,IF(LEN('PPI DATA'!V10)>0,'PPI DATA'!V10,IF(LEN('PPI DATA'!V12)>0,'PPI DATA'!V12,"Working")))))))))))))))

But i can't find a way for the cells to disregard the cells already listed so what i get is
Sheet 2
Break
Break
Break

Also i can only looking through as many cells as i can make if statements for which is 64 but i will need to have it search through up to 600 cells. i'm considering making another sheet so that the information is listed together even with blanks so that maybe if the cells aren't scattered i may be able to use a different function. any suggestions would be greatly appreciated

EggHeadCafe - Software Developer Portal of Choice
Silverlight 2 RC0 Doing Data Part VIII : Using the Threadpool
http://www.eggheadcafe.com/tutorial...4-e7c3d0508052/silverlight-2-rc0-doing-d.aspx

 

[Ashish Mathur]

Try this

1. Assign header rows to the data (if there is no header row). I.e. in
F7:J7, type Heading1, Heading2, heading3, Heading4, Heading5
2. Select the range (including the header row) I.e. F7:J12 and assign it a
name, say dummy
3. Select the range (including the header row) I.e. F7:J12 and convert it to
a table (Ctrl+L)
4. Click on a blank cell and save the file (say try.xls), assume on the
desktop
5. Now click on any blank cell and go to
In Excel 2003: Data > Import External Data > New Database query
In Excel 2007: Data > Get External Data > From Other Sources > From
Microsoft Query
6. Select Excel files and click on OK
7. Navigate to the desktop and select try.xls in the left had pane. Click
on Next
8. In the Choose columns dialog box, select dummy and click on the greater
then symbol
9. Click on Next three times and select View or Edit Query in MS Query
10, Click on the SQL button and delete whatever you see there. Type the
following in that box:

select heading1 from dummy
union
select heading2 from dummy
union
select heading3 from dummy
union
select heading4 from dummy
union
select heading5 from dummy

11. Click on OK/Finish
12. Go to File > Return Data to MS Office Excel
13. You should now see the import data box (if it does not appear, then
press Alt+tab to switch to a different window and return to try.xls)
14. Select Table and the cell where you want the output
15. Now if a blank appears in the output, then apply a normal auto filter
and uncheck the box for blanks

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com