how do i search non-adjacent cells in one worksheet and return those values and others into a second worksheet in excel 2010?

[tggrchc]

I have an Excel 2010 workbook where I need to perform the following task: I am trying to search for non-specific values in several non-adjacent cells in one worksheet and return those values and additional cell values in another worksheet.

Logic</SPAN>

1. In worksheet 1 search/loop through columns P, AA, AE, AI, AM, AQ, AU, AY, BC, BG, BK, BO, BS, BW, CA, CE, CI, CM, CQ, CU, CY, DC, DG, DK, DO, DS, DW, EA, EE, EI, EM, EQ, EU.</SPAN>
2. If any of these have a value in them, print in worksheet 2:</SPAN>
   1. Value in columns B, F, G, H, J, K, L</SPAN>
   2. Return the value in row 8 of the column where the value is found minus the first 8 characters (example, if value is found in column P, return value in cell P8 less first 8 characters)</SPAN>
   3. Value found in searched columns (listed above)</SPAN>
   4. Value in three columns following the found value (example: if value found in P, print Q, R, S) NOTE: these columns have formulas in them so I need them returned only if there is a value

</SPAN>
NOTE: in many cases there will be multiple values found for each row. I need each value found to display separately in worksheet 2 even if they are in the same row in worksheet 1. (Example, if row 27 in worksheet 1 has 4 values found in the above listed columns, I need 4 rows populated in worksheet 2, one for each value found.
</SPAN>
Example output in worksheet 2:</SPAN>

C</SPAN>	D</SPAN>	E</SPAN>	F</SPAN>	G</SPAN>	H</SPAN>	I</SPAN>	J</SPAN>	K</SPAN>	L</SPAN>	M</SPAN>	AM</SPAN>
B27</SPAN>	F27</SPAN>	G27</SPAN>	H27</SPAN>	J27</SPAN>	K27</SPAN>	L27</SPAN>	P8</SPAN>	P27</SPAN>	Q27</SPAN>	R27</SPAN>	S27</SPAN>
B27</SPAN>	F27</SPAN>	G27</SPAN>	H27</SPAN>	J27</SPAN>	K27</SPAN>	L27</SPAN>	AM8</SPAN>	AM27</SPAN>	AN27</SPAN>	AO27</SPAN>	AP27</SPAN>
B27</SPAN>	F27</SPAN>	G27</SPAN>	H27</SPAN>	J27</SPAN>	K27</SPAN>	L27</SPAN>	BC8</SPAN>	BC27</SPAN>	BD27</SPAN>	BE27</SPAN>	BF27</SPAN>
B27</SPAN>	F27</SPAN>	G27</SPAN>	H27</SPAN>	J27</SPAN>	K27</SPAN>	L27</SPAN>	CU8</SPAN>	CU27</SPAN>	CV27</SPAN>	CW27</SPAN>	CX27</SPAN>
B106</SPAN>	F106</SPAN>	G106</SPAN>	H106</SPAN>	J106</SPAN>	K106</SPAN>	L106</SPAN>	P8</SPAN>	P106</SPAN>	Q106</SPAN>	R106</SPAN>	S106</SPAN>
B106</SPAN>	F106</SPAN>	G106</SPAN>	H106</SPAN>	J106</SPAN>	K106</SPAN>	L106</SPAN>	EI8</SPAN>	EI106</SPAN>	EJ106</SPAN>	EK106</SPAN>	EL106</SPAN>
B239</SPAN>	F239</SPAN>	G239</SPAN>	H239</SPAN>	J239</SPAN>	K239</SPAN>	L239</SPAN>	DK8</SPAN>	DK239</SPAN>	DL239</SPAN>	DM239</SPAN>	DN239</SPAN>
B1011</SPAN>	F1011</SPAN>	G1011</SPAN>	H1011</SPAN>	J1011</SPAN>	K1011</SPAN>	L1011</SPAN>	CE8</SPAN>	CE1011</SPAN>	CF1011</SPAN>	CG1011</SPAN>	CH1011</SPAN>

<TBODY>
</TBODY>

Currently, in WS2 I have all accounts (rows) from WS1 listed 33 times each (one for each status I am searching in the columns listed above). I have a separate column in WS2 called "Engaged?" where I am using VLOOKUP and MATCH to determine if each column listed (in WS1) above is populated.

=IF(VLOOKUP($C3704,AE,MATCH(" Status Lean",AE[#Headers],0),FALSE)<>" ","Y"," ")

The rest of the columns in WS2 are populated using VLOOKUP and MATCH if the "Engaged?" column contains a "Y".

=IF(K3704="Y",VLOOKUP($C3704,AE,MATCH(" Start Lean",AE[#Headers],0),FALSE)," ")

This strategy accomplishes my goal, however, worksheet2 has over 75,000 rows due to the duplicate account listings (both engaged and not engaged) and the file size is over 51MB. I am hoping there is an alternate solution that only pulls the rows I need from WS1, therefore reducing the file size. If this is possible, it will reduce WS2 from 77,597 rows to 1773 rows.</SPAN></SPAN>

 

[jsotola]

how many rows are you searching on sheet1 ?

 

[tggrchc]

Currently about 2500 rows, but will probably stretch to around 4000.

 

[jsotola]

ok, now i am confused

do the the 2500+ rows on the source sheet start after row 8 ???

what about an instance of multiple hits in one row on the source sheet?

 

[tggrchc]

yes, the rows I need searched in the source sheet start after row 8 (row 8 is the header row for the table). There will be multiple hits in some rows on the source sheet. I need worksheet 2 to display all hits from every row in their own new rows as shown in the table above.