Starting again from a different approach

[Horkenheimer]

Hi there,

Thanks to Fazza, I am going to start again with a clear description of my intended goal and the size of the project. I need to select some key information and combine two enormous worksheets of 100,000 plus rows and 15 plus columns.
1) The first task is the following. On one worksheet I need to keep column A and B. I need to then align the first large number in one column (highlighted in yellow) and the second large numbers in the next column, etc. Every row has at least one large number. (One row has 30 large numbers, so I will need a total of 30 columns of large numbers). This number is a reference number. Everything else will be erased.

index_nouns_V1.1.xltm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
140	a	n	#s	#p	%p	;	7	1	13679721	15114370	14853735	14731050	13658855	6844227	5408203
141	a'man	n	0	8363213
142	a-bomb	n	1	0	2756049
143	a-horizon	n	0	8676100
144	a-line	n	2700080
index

I was kindly offered the formula =IFERROR(INDEX($F142:$AH142,0,SMALL(IF(LEN($F142:$AH142)>3,COLUMN($F142:$AH142)),COLUMNS($AI142:AO142))-COLUMN($F142)+1),"") (see column AP in the .xltm below) for the above task and it worked perfectly. But I had to fill 30 columns and 100,000 rows with the formula, which made the worksheet extremely heavy and it keeps crashing the program every time I attempt to clean up the file by clearing the seemingly blank cells (in which the formula evaluates to "") and switching the other cells to values.

2) The next large task is to connect each reference number (highlighted) with an associated code which is located on another sheet. Ultimately, the hope is to have each large number have an adjacent column with the code number. I have started this task in the second .xltm, as you can see.

For this task I used: =VLOOKUP(AI1,'/Users/kedrickjames/Desktop/dict 2/[data_data_connected.xls]data'!$A:$B,2)

Index_nouns_V3.xlsx
	AI	AJ	AK	AL	AM	AN	AO	AP
142	13679721	23	15114370	14853735	14731050	13658855	6844227	5408203
143	8363213	14
index_organized

Cell Formulas
Range	Formula
AJ142:AJ143	AJ142	=VLOOKUP(AI142,'/Users/kedrickjames/Desktop/dict 2/[data_data_connected.xls]data'!$A:$B,2)
AP142:AP143	AP142	=IFERROR(INDEX($F142:$AH142,0,SMALL(IF(LEN($F142:$AH142)>3,COLUMN($F142:$AH142)),COLUMNS($AI142:AO142))-COLUMN($F142)+1),"")
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

The target lookup sheet has 82221 rows. Each large number has an associated code in column B that I need, as you can see below:

data_nounsV1.1.txt
	A	B
25	21007	3
26	21445	3
27	21914	3
28	22119	3
29	23083	3
30	23280	3
31	23451	3
32	23953	3
33	24444	3
34	24900	3
35	26390	3
36	27365	3
37	28005	3
data_nounsV1.1

3. ULTIMATELY, I will end up with column A & B (from the first graph) then 30 columns containing only the codes (in the end I won't need the large reference numbers)...

Does this make sense? Any guidance on this project would be extremely helpful. The problem is the files are so big they keep crashing my BRAND NEW desktop Mac... So maybe I need to start again and approach this differently?

Thanks in advance!!!!

 

[KRice]

Could you clarify some details for me, please? You have a large table that you'd like to review row-by-row, beginning in column C and moving to the right, with the objective to identify all large numbers in that row. Those large numbers are to be consolidated somewhere, beginning at some column far to the right in the same row. If more than one large number is found in the row, then those numbers are shown in sequential columns to the right. Is this correct for Step 1?

How is a "large number" defined...is it any number greater than some threshold?
Do the rows (after column B) contain only numeric data?
Is this task to be done only once, as might be the case if cleaning up a data set, or is it to be repeated regularly?
I considered suggested the possibility of transposing the entire table and then sorting the results from largest to smallest to bring the largest values to the top of the table, but you'd have to break the original table up into manageable pieces to work within maximum column limits of Excel.

Would a simpler formula help with the crash issues?....or perhaps store the largest 30 values from each row in an array? Below is a notional example using the simpler LARGE function.

MrExcel_2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Notional starting table										largest values
2											1	2	3	4	in array
3	keep A3	keep B3	956	811	555	670	542	222	415		956	811	670	555	956
4	keep A4	keep B4	684	692	724	590	873	963	460		963	873	724	692	963
5	keep A5	keep B5	803	295	911	168	526	552	321		911	803	552	526	911
6	keep A6	keep B6	479	828	396	715	818	376	669		828	818	715	669	828
7	keep A7	keep B7	862	843	268	468	153	771	591		862	843	771	591	862
8	keep A8	keep B8	557	678	442	881	313	103	119		881	678	557	442	881
9	keep A9	keep B9	846	444	637	549	659	944	583		944	846	659	637	944
Sheet20

Cell Formulas
Range	Formula
K3:N9	K3	=LARGE($C3:$I3,K$2)
O3:O9	O3	=LARGE($C3:$I3,{1;2;3;4})
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[Horkenheimer]

Could you clarify some details for me, please? You have a large table that you'd like to review row-by-row, beginning in column C and moving to the right, with the objective to identify all large numbers in that row. Those large numbers are to be consolidated somewhere, beginning at some column far to the right in the same row. If more than one large number is found in the row, then those numbers are shown in sequential columns to the right. Is this correct for Step 1?

How is a "large number" defined...is it any number greater than some threshold?
Do the rows (after column B) contain only numeric data?
Is this task to be done only once, as might be the case if cleaning up a data set, or is it to be repeated regularly?
I considered suggested the possibility of transposing the entire table and then sorting the results from largest to smallest to bring the largest values to the top of the table, but you'd have to break the original table up into manageable pieces to work within maximum column limits of Excel.

Would a simpler formula help with the crash issues?....or perhaps store the largest 30 values from each row in an array? Below is a notional example using the simpler LARGE function.

MrExcel_2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Notional starting table										largest values
2											1	2	3	4	in array
3	keep A3	keep B3	956	811	555	670	542	222	415		956	811	670	555	956
4	keep A4	keep B4	684	692	724	590	873	963	460		963	873	724	692	963
5	keep A5	keep B5	803	295	911	168	526	552	321		911	803	552	526	911
6	keep A6	keep B6	479	828	396	715	818	376	669		828	818	715	669	828
7	keep A7	keep B7	862	843	268	468	153	771	591		862	843	771	591	862
8	keep A8	keep B8	557	678	442	881	313	103	119		881	678	557	442	881
9	keep A9	keep B9	846	444	637	549	659	944	583		944	846	659	637	944
Sheet20

Cell Formulas
Range	Formula
K3:N9	K3	=LARGE($C3:$I3,K$2)
O3:O9	O3	=LARGE($C3:$I3,{1;2;3;4})
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

Thanks for your response, KRice. To clarify, each row is a word that has one to thirty (at the very most) values that are attached to each. I need to tidy this up so that the first value of each word is in one column, the second value is in the second column etc.
I will then create an adjacent column in which to put the code associated with each of those values.

index_V2.xlsx
	A	B	C	D	E	F	G	H	I	J
50680	head	n	5546258	8	1320872		5619057	10182584	8499282	11516007
50681	head-shrinker	n	10507601	18	10183045
50682	head_ache	n	14350016	20
50683	head_blight	n	14240955	20
50684	head_cabbage	n	11896407	20	7730316
50685	head_cabbage_plant	n	11896407	20
50686	head_cold	n	14169241	20
index_organized

In the above graph column C is the first value, column D is the code associated with column C. Column E is the second value and column F will be the code associated with Column E, and so on. (In this table I have not yet used the VLOOKUP to fill column F or insert columns beside G, H, I, & J)

I was wondering if instead of using the function I have used to organize these columns (which has made this file so big it's almost unusable) I should instead create a database with macros (I am totally new to this!!!) rather than inserting functions for all of this. As you can see in the above graph, there needs to be functions in all the cells of columns EFGHIJ to catch any values, even though most often there will be no value at all. Every row has one value, way less of the rows of 2, 3, 4 values etc. Only one row (of 100,000 rows) has 30 values.
If I keep going with this methodology, should I combine the pages into one document for VLOOKUP? Maybe that would cutdown on file size?
Does this make sense?

 

[KRice]

It sounds as if you have data sets that might be better managed with a database. But if you stay with Excel, I wonder why the first table is being constructed as you've described. For example, will data in column D ever be used? Since column D holds small values associated with Column C, but you want to extract the "large" value in Column C, is it even necessary to include the information in Column D when the table is first built?

Rather than building a large table with VLOOKUP functions that return many null results, and then "re-process" the table to purge it of extraneous information and blank cells, you might consider using VBA to construct the initial table...not to modify the current unwieldy table, but to use VBA to perform the look-ups from whatever sources they are drawn from and to write only the results that are desired into a consolidated table. If that sounds like it might be a more preferred approach, I would encourage you to post small working samples from your data sources (overwritten with other data if the original contains sensitive information) to request help with some code to pull the data into a consolidated table structure that you can use. With this approach, you wouldn't have 30 x 100,000+ formulas in the worksheet, and that should allow you to move forward with a manageable table that doesn't cause (hopefully) the crash/slowness issues.