Macro to pull imported data from sheet2 and place data in specific rows on sheet1

[PatRiot199]

Hello Folks,

I have a worksheet that will have data imported into sheet 2, but the number of rows between one set of data values, or Source values, and the other set of data values, or Target values, vary from one imported spreadsheet to the next. The values of the headers for each grouping are standard though. So I need the macro to find the row that contains the string "Configuration for R4(B)(C) sites", then find the string "Site USID" and copy all rows down to the row just above the row that contains the string "New Target IP Configuration" and place them in sheet 1 in the range A4:I19. Then do the same for the data for the Target configuration.

I need to be able to pull data from these based on the site value that is selected, so I need these values to be found in a consistent location so I can use VLOOKUP effectivity to pull the correct data for the selected site.

I'm hoping someone has a macro that can fullfill these requirements as I've been stumped on how to do this for some time.

Attached is a sample worksheet with sample data populated into sheet 2. MacroTest1.xlsm

I will be eternally grateful to anyone that can help me here.

Thanks in advance,
Patrick

[watersev]

hi Patrick, formula option, if I understood your request correctly

[PatRiot199]

hi Patrick, formula option, if I understood your request correctly

Thanks for the quick reply watersev!

That almost gets me there, but I confused you because the spreadsheet I shared does not have the tab that will contain the provisioning commands that will use the data presented in Sheet 1. So I need the Site USID column to automatically populate along with the rest of the values in that row. Ideally even perhaps, pick and choose which columns of data I display, but that's not a requirement.

Thanks again, but can you think of a way to populate the USID column without user interaction for each row?

[watersev]

sort of that then

[PatRiot199]

Thank you again watersev, this is great! I never expected formulas to be this flexible with data retrieval.

I have one more wrinkle for you. When I posted my example I did it in its most simple form thinking I can manipulate the response to meet all of my needs to pull data from multiple sheets. After trying to manipulate the formula you provided to append data from another sheet, Sheet 3, to the Sheet 1 data that is already being pulled from Sheet 2 I found I'm unable to get it working.

Will you take a look at the new workbook I attached and let me know if there is a way to consolidate the data from multiple sheets that apply to a single site to display on a single row using your same formula method?

Thank you again for your time and efforts in helping me with this.

[watersev]

please check attachment

[PatRiot199]

Thanks for all your help watersev, I was able to accomplish my task with your help.