0

I have a long excel file and more than one column data depends on data validation in a other sheet which is parameters. After a while I need to edit some of these list. For example I won't use names from now on I will use ids instead of name. So; Andrea become 22 which is his id.

But I need to update whole list (more than 100k lines). When I update parameters, new values are applicable for new entries but as I said, I need to update all of it with existing entries.

This is how main page looks like

users department cost city ...
John Logistics $350 Rome ...
Sarah Engineering $480 Rome ...
Mark Incoming $110 Venice ...
... ... ... ...

This is the parameters table and main page get data from here by data verification list property.

users department
John logistics
Alex incoming
Suzan logistics

And this is the new parameters table. Instead of names, I want to use new ids.

users department
22 13
13 8
7 13

But I want to refresh the old cells with new values. I want to change all logistics with 13, all Johns with 22 etc...

Since there are too many columns and too many entries in parameters, I don't want to use vlookup etc.

I'm looking for a really simple solution. Don't have any experience with vba or macros etc.

Vito Andolini
  • 99
  • 7
  • 1
    How both screenshots are related? What are the input and expected results? Please use table markdown format. You can use this [tool](https://www.tablesgenerator.com/markdown_tables). Thanks! – David Leal Mar 09 '23 at 16:48
  • Thank you for suggestion. I edited the question and I hope it explains better. – Vito Andolini Mar 10 '23 at 06:59
  • You can do it, but in a different cell range to provide the output. If you want to modify the input without putting the result in a different place, then you need VBA for that, it helps you to transform your original dataset. Using excel functions you cannot put the output in the same place where you have the input data. – David Leal Mar 11 '23 at 16:25
  • @DavidLeal do you have any example file with VBA? I may modify and use it. – Vito Andolini Mar 12 '23 at 15:47
  • I am not a VBA expert, I try to avoid it whenever I can, but it is also risky (even from security standpoint, some companies block the use of macros), because you loose the source data after the VBA call, unless you make a copy of your dataset. In general I prefer to keep the original source, then make transformations and generate the output in a different range, like in any ETL process. For that you can use Power Query or excel functions. – David Leal Mar 12 '23 at 16:04

0 Answers0