Copy info between sheets based on cell names and criteria

Sparty30

New Member
Joined
Apr 27, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
Long time lurker but first time poster since haven't found my answer (or at least nothing I can get to work). I'll try to explain what I'm trying to do here.

I'm pulling in data from 2 different sites that use the same name for a data set and each is going into separate sheets in my file. What I want to do is transfer select columns from one sheet into a new sheet based on the data set name and a criteria from the first sheet. Here's example since sure that's not real clear.

Data on 'sheet 1' that comes in. One thing to note is the order of the samples can be random when the data comes in so 'Sample 1' may not always come in row 2. I can probably sort that but would prefer to just link everything by the "Sample" name.
1588006642771.png


Data on 'sheet 2' that comes in. 'Sample 1' on sheet 1 is same data set as 'Sample 1' on sheet 2 so that name is the common thread between the sheets.
1588006286719.png


In this example what I'd like to do is copy the column of data on sheet 2 based on sheet 1 criteria being met. I can't figure out how to link a name between different sheets and have the formula automatically copy just those columns from sheet 2 over. For example this would be a sample of sheet 3 output I want if I wanted only data for samples greater than 55 on sheet 1
1588006594059.png


Is there some array or other function I can use that would automatically populate sheet 3 when sheets 1 & 2 data are updated based on a common naming convention? I've been manually copying columns over but that's getting crazy since getting more data points. Thanks a ton for any help.
 

Attachments

  • 1588006168975.png
    1588006168975.png
    6.5 KB · Views: 3

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Please provide your sample data using the XL2BB function. We cannot manipulate pictures.
 
Upvote 0
Sorry, hopefully this works (never uses XL2BB function before). Thanks again for looking

Sheet 1
Excel Sample.xlsx
C
19
Sheet1


Sheet 2
Excel Sample.xlsx
D
20
Sheet2


Sheet 3
Excel Sample.xlsx
D
12
Sheet3
 
Upvote 0
What ever you copied is blank. You will need to highlight each entire range and then apply the XL2BB function.

Here is an example of me copying 5 cells and pasting here with XL2BB. The content is immaterial.

Book1
A
11/1/2020
21/2/2020
31/3/2020
41/4/2020
51/5/2020
Sheet1
 
Upvote 0
Haha, told you I was new to this. Sorry again.

Let's try again; did separate sheets again because not sure how to capture all the sheets.

Sheet 1
Excel Sample.xlsx
AB
1DescriptionPercentage
2Sample 135
3Sample 250
4Sample 360
5Sample 440
6Sample 580
7Sample 675
Sheet1


Sheet 2
Excel Sample.xlsx
ABCDEFG
1ResultSample 1Sample 2Sample 3Sample 4Sample 5Sample 6
2Test 1aabacb
3Test 2bbcacb
4Test 3cabbbc
5Test 4aaabaa
6Test 5caabaa
Sheet2


Sheet 3
Excel Sample.xlsx
ABCD
1Would like something like this automatically based on "Sample #" naming convention
2ResultSample 3Sample 5Sample 6
3Test 1bcb
4Test 2ccb
5Test 3bbc
6Test 4aaa
7Test 5aaa
Sheet3
 
Upvote 0
Using Power Query, I was able to recreate your desired results.
Mcode for first table
VBA Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Description", type text}, {"Percentage", Int64.Type}}),
    #"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}, {"Column2", type any}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type1"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers",{{"Description", type text}, {"Sample 1", Int64.Type}, {"Sample 2", Int64.Type}, {"Sample 3", Int64.Type}, {"Sample 4", Int64.Type}, {"Sample 5", Int64.Type}, {"Sample 6", Int64.Type}})
in
    #"Changed Type2"
Mcode for second table
VBA Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Result", type text}, {"Sample 1", type text}, {"Sample 2", type text}, {"Sample 3", type text}, {"Sample 4", type text}, {"Sample 5", type text}, {"Sample 6", type text}})
in
    #"Changed Type"

With both tables set, I appended the second table to the first and applied this Mcode to get the expected results
VBA Code:
let
    Source = Table.Combine({Table1, Table2}),
    #"Removed Columns" = Table.RemoveColumns(Source,{"Description"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Result", "Sample 1", "Sample 2", "Sample 3", "Sample 4", "Sample 5", "Sample 6"}),
    #"Replaced Value" = Table.ReplaceValue(#"Reordered Columns",null,"Percentage",Replacer.ReplaceValue,{"Result"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Replaced Value",{"Sample 1", "Sample 2", "Sample 4"})
in
    #"Removed Columns1"

Book1
ABCD
1ResultSample 3Sample 5Sample 6
2Percentage608075
3Test 1bcb
4Test 2ccb
5Test 3bbc
6Test 4aaa
7Test 5aaa
Sheet3
 
Upvote 0
Thanks! It worked for me on my sample data. Now I (think I) understand the concept so can apply it to actual data sets. Thanks again
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

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
Back
Top