VBA/some combo of functions to dynamically copy data based on values (that are duplicated)

Kathy199

New Member
Joined
Jun 21, 2021
Messages
1
Office Version
  1. 2019
Platform
  1. Windows
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
source.PNG
Pic 2 where values from the first pic must be dynamically copied into rows underneath "7. job environment" in this sheet
target.PNG


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
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Forum statistics

Threads
1,144,342
Messages
5,723,821
Members
422,518
Latest member
quack_quack

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