Hi there,
I'm stack in a loop where I think I cannot find any further answers myself and I hope to get some help here.
I have a worksheet with two sheets 1 being the source of data where I have job information and the other - sheet 2 - is my target sheet which must be a form with all the job information copied into correct places.
Pic 1 where "7. job environment" is in column A is my source
Pic 2 where values from the first pic must be dynamically copied into rows underneath "7. job environment" in this sheet
Basically, I need to handle duplicates for a specific job title (in the future in sheet from pic 1 there will be lots of other posts so this is another things that bugs me) and so far I have tried a modified version of Vlook up using offset to get the indices of different values adjacent to what I am looking for (e.g. "7. Job environment" in rows A5:A9 gives me values in column B5:B9) but the form did not work if I added suddenly another row in e.g. row 10 where as my "7. Job environment" I pasted Test/Whatever. The form did not update dynamically.
For the reference the formula: =INDEX(Sheet1!$B$9:$B$63;AGGREGATE(15;3;(Sheet1!$A$9:$A$63=Sheet2!$A$14)/(Sheet1!$A$9:$A$63=Sheet2!$A$14)*ROW(Sheet1!$A$9:$A$63)-(ROW(Sheet1!$A$8));ROWS(Sheet2!$A$15:A16)))
Therefore I think I need some code, I know some Python but I need to use VBA which I had never used. My algorithm is kinda this:
1. Hardcode an array of possible duplicates e.g. ["7. Job environment", "8. Requirements" ]
2. Go through this array and for each item, perform:
a. Find the current array element in Sheet 1 (source) and grab value from the adjacent column OR maybe grab the whole range at once?
b. Keeping this result array/range in memory, find now the same array element in Sheet 2 (target). Once found, paste the values kept in memory - each into a new row
Keep going untill all is copied... It could be either a function or just VBA Sub that runs when a user clicks a button to call it. I need this dynamic aspect of being able to suddenly add a new item in source.
My issue is how to exactly do it and if I am being too complicated nad unrealistic....
Sorry for bad formating, I think I lost my brain on reading all the available posts and coming up with no answers anymore!
Many thanks,
Kath
I'm stack in a loop where I think I cannot find any further answers myself and I hope to get some help here.
I have a worksheet with two sheets 1 being the source of data where I have job information and the other - sheet 2 - is my target sheet which must be a form with all the job information copied into correct places.
Pic 1 where "7. job environment" is in column A is my source
Basically, I need to handle duplicates for a specific job title (in the future in sheet from pic 1 there will be lots of other posts so this is another things that bugs me) and so far I have tried a modified version of Vlook up using offset to get the indices of different values adjacent to what I am looking for (e.g. "7. Job environment" in rows A5:A9 gives me values in column B5:B9) but the form did not work if I added suddenly another row in e.g. row 10 where as my "7. Job environment" I pasted Test/Whatever. The form did not update dynamically.
For the reference the formula: =INDEX(Sheet1!$B$9:$B$63;AGGREGATE(15;3;(Sheet1!$A$9:$A$63=Sheet2!$A$14)/(Sheet1!$A$9:$A$63=Sheet2!$A$14)*ROW(Sheet1!$A$9:$A$63)-(ROW(Sheet1!$A$8));ROWS(Sheet2!$A$15:A16)))
Therefore I think I need some code, I know some Python but I need to use VBA which I had never used. My algorithm is kinda this:
1. Hardcode an array of possible duplicates e.g. ["7. Job environment", "8. Requirements" ]
2. Go through this array and for each item, perform:
a. Find the current array element in Sheet 1 (source) and grab value from the adjacent column OR maybe grab the whole range at once?
b. Keeping this result array/range in memory, find now the same array element in Sheet 2 (target). Once found, paste the values kept in memory - each into a new row
Keep going untill all is copied... It could be either a function or just VBA Sub that runs when a user clicks a button to call it. I need this dynamic aspect of being able to suddenly add a new item in source.
My issue is how to exactly do it and if I am being too complicated nad unrealistic....
Sorry for bad formating, I think I lost my brain on reading all the available posts and coming up with no answers anymore!
Many thanks,
Kath
