Assignment of a value to a entry in a big dataset

[peperosso]

Hi everyone!

I have a sheet with thousand of records and I have to run a specific function on one of the columns. The latter contains a large number of duplicates. I set up a new function with VB, but I cannot run it on all the data due to the required computational effort. Therefore I thought to create a new column without replicates, and to run the function on this new column.

However, I do not have a clue on how I can assign the new values (obtained running the function on the new column) to the entries of the original table. I thought to do that with a series of nested IF, but it would be simply impossible due to the high amount of data.

Here a simple table to exemplify my problem:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Key[/TD]
[TD]City (with replicates)[/TD]
[TD]Preferred food of the citizens[/TD]
[TD][/TD]
[TD]City (without replicates)[/TD]
[TD]Preferred food of the citizens[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Chicago[/TD]
[TD][/TD]
[TD][/TD]
[TD]Chicago[/TD]
[TD]Hamburger[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Londra[/TD]
[TD][/TD]
[TD][/TD]
[TD]New York[/TD]
[TD]Pizza[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Roma[/TD]
[TD][/TD]
[TD][/TD]
[TD]Roma[/TD]
[TD]Pasta[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Londra[/TD]
[TD][/TD]
[TD][/TD]
[TD]Londra[/TD]
[TD]Fish&Chips[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Roma[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Roma[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]New York[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Londra[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Chicago[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

As you can see in the table, I have a list of rows (identified by a keys), each of which has an attribute "city". Suppose that with my VB function I am able to calculate the preferred food of the inhabitants given the name of the city. The function is quite expensive and I cannot run it over all my entries: therefore I create a new column without replicates, and I run the function on this new set. Then I have to re-assign the outcome of the functions to my initial set of rows...
Any suggestion on how I should do?

Thank you in advance for your time!

 

[Fishboy]

Hi everyone!

I have a sheet with thousand of records and I have to run a specific function on one of the columns. The latter contains a large number of duplicates. I set up a new function with VB, but I cannot run it on all the data due to the required computational effort. Therefore I thought to create a new column without replicates, and to run the function on this new column.

However, I do not have a clue on how I can assign the new values (obtained running the function on the new column) to the entries of the original table. I thought to do that with a series of nested IF, but it would be simply impossible due to the high amount of data.

Here a simple table to exemplify my problem:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Key[/TD]
[TD]City (with replicates)[/TD]
[TD]Preferred food of the citizens[/TD]
[TD][/TD]
[TD]City (without replicates)[/TD]
[TD]Preferred food of the citizens[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Chicago[/TD]
[TD][/TD]
[TD][/TD]
[TD]Chicago[/TD]
[TD]Hamburger[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Londra[/TD]
[TD][/TD]
[TD][/TD]
[TD]New York[/TD]
[TD]Pizza[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Roma[/TD]
[TD][/TD]
[TD][/TD]
[TD]Roma[/TD]
[TD]Pasta[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Londra[/TD]
[TD][/TD]
[TD][/TD]
[TD]Londra[/TD]
[TD]Fish&Chips[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Roma[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Roma[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]New York[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Londra[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Chicago[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

As you can see in the table, I have a list of rows (identified by a keys), each of which has an attribute "city". Suppose that with my VB function I am able to calculate the preferred food of the inhabitants given the name of the city. The function is quite expensive and I cannot run it over all my entries: therefore I create a new column without replicates, and I run the function on this new set. Then I have to re-assign the outcome of the functions to my initial set of rows...
Any suggestion on how I should do?

Thank you in advance for your time!

Hi peperosso, welcome to the boards.

Based on your example above, once you have the shortened list without duplicates, you can do a VLOOKUP in your original table. I used the following formula to produce the results shown below:

=VLOOKUP(B2,$E$2:$F$5,2,FALSE)

Bold green B2 is the lookup value (city)
Bold red $E$2:$F$5 is the table it is looking up from (the table without duplicates)
Bold blue 2 is the column in the lookup table you want to get the result from (preferred food of the citizens)
​Bold orange False tells the VLOOKUP you want an exact match

Excel 2010

	A	B	C	D	E	F
Key	City (with replicates)	Preferred food of the citizens	City (without replicates)	Preferred food of the citizens
Chicago	=VLOOKUP(B2,$E$2:$F$5,2,FALSE)	Chicago	Hamburger
Londra	Fish&Chips	New York	Pizza
Roma	Pasta	Roma	Pasta
Londra	Fish&Chips	Londra	Fish&Chips
Roma	Pasta
Roma	Pasta
New York	Pizza
Londra	Fish&Chips
Chicago	Hamburger

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]4[/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

In the screenshot above I added a space before the formula in the first cell to make it show the actual formula rather than the result. The cells below that have the correct formula without the preceding space.

I hope this helps.

 

[peperosso]

Thank you Fishboy, it is exactly what I was searching for!!

 

[Fishboy]

Happy to help mate