Delete Lookup table but keep valuess in cells?

AndyGray

New Member
Joined
May 31, 2017
Messages
30
Hi guys, I've got a huge spreadsheet at the moment and it is growing (looking at around 900,000 rows upon completion. It is for a huge data migration procedure. I've got some V lookup tables in this, and some concatenated values which I have used for a vlookup... I want to delete this row as it will break my migration but keep the values that this row has populated?

Is it possible to delete these cells without actually effecting my lookups? I know i can obviously copy and paste the entire sheet into a new spreadsheet and just paste the values, but I think once this sheet is done it may crash excel. Any suggestions are welcome. As this will be getting exported as a tab deliminated CSV I can always format the full sheet to remove formulas and leave values if there is a way to do this also which would solve the problem.

Thanks.
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Youngdand

Board Regular
Joined
Sep 29, 2017
Messages
120
is there anything regular in these concatenation helper rows which you can use to filter on? even possibly blanks, were to would be data in a correct row.

If so, save a copy CSV, then reopen, filter and just delete the helper rows.
 
Last edited:

AndyGray

New Member
Joined
May 31, 2017
Messages
30
Theres nothing really regular in this sheet. Some of the formulas are concatenation rows, some of them are V lookups, some of them are =A1 etc.. So the full sheet needs stripping of it's formula to display only values. I know it is possible to do with copy and paste values but the sheet is very very unstable. Total count was 488,000 rows in sheet 1, numerous sheets are present. Every change bumps my processor to 100% usage for about 5 minutes.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,873
Messages
5,627,399
Members
416,245
Latest member
Xterminat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top