Apr 26 2021 02:45 AM - edited Apr 26 2021 03:37 AM
how do we fill in school.xlsx file age column from age.xlsx file by using name column with visual basic excel? Tables have variable length and have more than 10000 rows.
school.xlsx
|A | B | C|
|:---- |:------:| -----:|
|name | code | age|
|peter1 | 254 |
|jane2 |153 |
|ted | 323 |
|peter3 |323 |
age.xlsx
|A |B|
|:---- |:------:|
|name |age|
|mark3| 1,2|
|peter1 | 5 |
|jane2 | 1,5|
'''
Sub Insertdata()
Dim iAge As Integer
Set src=Workbooks.Open("age.xlsx", True, True)
iAge = src.Worksheets("Sheet1").Range("B2").Value
Worksheets("Sheet1").Range("C2").Value = iAge
End Sub
'''
Now the program returns value 1 in field C2 in age.xlsx file.
How do we remake the program to insert age data in right row in shcool.xlsx?
Apr 26 2021 02:57 AM
@sergey989 Why VBA? A simple VLOOKUP or INDEX/MATCH (or XLOOKUP if your Excel version supports it) can do this.
Apr 26 2021 03:01 AM
Apr 26 2021 03:36 AM
@sergey989 See attached example. I chose to put the data in structured tables. All three lookup methods are in the table at the top under age1, age2 and age3.
See if you can get it to work on your side.
Apr 26 2021 03:47 AM - edited Apr 26 2021 04:03 AM
can we make in two different files and very large and very large variable length table?
Apr 26 2021 03:58 AM
@sergey989 Yes you can. Look at the MS support pages for the function you want to use and follow the instructions there. Not much more I could do than repeat what has been written there already.
Apr 26 2021 04:25 AM - edited Apr 26 2021 05:02 AM
but excel stall when i copy large columns .
=ЕСЛИОШИБКА(ВПР([@[name ]];tblAge;2;0);"Not found")
Can we put this code in vba macros and how to make faster?
Apr 26 2021 06:14 AM
@sergey989 Please provide a bit more info, otherwise we will go back and forth without achieving anything. Perhaps you can upload some files that are like your real ones and then I can perhaps enter the required formulae. From Google translate I understand that you are going for "ВПР" = VLOOKUP
Apr 26 2021 06:18 AM - edited Apr 26 2021 06:19 AM
sample two files
how do we fill in school.xlsx file age column from age.xlsx file by using key name column with visual basic excel? Tables have variable length and have more than 10000 rows.
school.xlsx
Apr 26 2021 06:23 AM
@sergey989 Two files attached. This time I just used the data as you provided it. Didn't bother to create structured tables. You need to have both files open.
Apr 26 2021 08:00 AM - edited Apr 27 2021 03:58 AM
how do i make condition in cell for example that i have value of cell in shool.xlsm file and i don't want it will be lost but formula work only for those values that exist in age.xlsx? Another words new values.
how do we instead of Not found value in third column in shcool.xlsm file have previous value that was stored before from early version of file age.xlsx? example attach
for example, BRS43 now have value Not found but ealier it has value 2. How can we make that file school.xlsm store previous values and formula do not erase previous value and only update some rows with values from newer age.xlsx file.
Apr 26 2021 08:05 AM
@sergey989 Sorry, I don't understand. Perhaps @Sergei Baklan can help out. You can communicate with him in Russian.
Apr 26 2021 09:28 AM
Sorry, VBA is not my territory. If I understood correctly if the value is not found in age we keep it in school as it is, otherwise take value from age.
I'd use Power Query to combine data such way if that's suitable.
Apr 27 2021 12:18 AM - edited Apr 27 2021 12:20 AM
how do we instead of Not found value in third column in shcool.xlsm file have previuos value that was stored before from early version of file age.xlsx? example attach
for example BRS43 now have value Not found but ealier it has value 2. How can we make that file school.xlsm store previos values and formula do not earese previuos value and only update some rows with values from newer age.xlsx file.
Apr 27 2021 01:21 AM - edited Apr 27 2021 01:22 AM
how do we instead of Not found value in third column in shcool.xlsm file have previous value that was stored before from early version of file age.xlsx? example attach
for example, BRS43 now have value Not found but ealier it has value 2. How can we make that file school.xlsm store previous values and formula do not erase previous value and only update some rows with values from newer age.xlsx file. New verion of file included.
Apr 27 2021 03:50 AM
Apr 27 2021 04:10 AM
Apr 27 2021 10:44 AM
If to keep previous value in stack and return into the same cell result of calculation or previous value, depends on condition, that's only with VBA. Can't help with it.
If Power Query it'll be one more table which merge these two ranges and return the value from one or another depends on they match or not. Mockup is in attached file.
May 03 2021 01:13 AM - edited May 03 2021 01:14 AM
How dose file school.xlsm was made with power query?
how does Mockup make?