Extracting Certain Conditions from a Master Worksheet.

N

NucEng

New Member
Joined
Nov 17, 2013
Messages
2
So I am a engineer running a test that usually has 150 conditions. Every odd condition is a "equilibrium condition" that is meant to stabilize the test rig between the even conditions. The even conditions are the actual results I want to look at and evaluate.


I've been trying to use a IF functions or Vlookup to extract the even conditions into a separate worksheet automatically. But I can't seem to figure out how to grab the information I want automatically.


So basically I want to take all of the corresponding values for condition 2 (the whole row of values) and put them in a separate worksheet. A repeat for every even condition, regardless of how long it is or how long the other conditions are. Sometimes the data points are taken at different times and so having a template that works regardless of the amount of data would be amazing.


I included a sample of what it looks like. ( very basic, usually the conditions are much longer.)


Thanks for your help!!! I've watched hours of tutorial videos and I can't seem find a easy way to do this automatically.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Condition[/TD]
[TD]Data 1[/TD]
[TD]Data2[/TD]
[TD]Data 3[/TD]
[TD]Data 4[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]100[/TD]
[TD]24[/TD]
[TD]54[/TD]
[TD]654[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]104[/TD]
[TD]29[/TD]
[TD]40[/TD]
[TD]677[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]150[/TD]
[TD]40[/TD]
[TD]90[/TD]
[TD]700[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]155[/TD]
[TD]42[/TD]
[TD]88[/TD]
[TD]721[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]210[/TD]
[TD]80[/TD]
[TD]200[/TD]
[TD]900[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
NucEng said:
So I am a engineer running a test that usually has 150 conditions. Every odd condition is a "equilibrium condition" that is meant to stabilize the test rig between the even conditions. The even conditions are the actual results I want to look at and evaluate.


I've been trying to use a IF functions or Vlookup to extract the even conditions into a separate worksheet automatically. But I can't seem to figure out how to grab the information I want automatically.


So basically I want to take all of the corresponding values for condition 2 (the whole row of values) and put them in a separate worksheet. A repeat for every even condition, regardless of how long it is or how long the other conditions are. Sometimes the data points are taken at different times and so having a template that works regardless of the amount of data would be amazing.


I included a sample of what it looks like. ( very basic, usually the conditions are much longer.)


Thanks for your help!!! I've watched hours of tutorial videos and I can't seem find a easy way to do this automatically.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Condition[/TD]
[TD]Data 1[/TD]
[TD]Data2[/TD]
[TD]Data 3[/TD]
[TD]Data 4[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]100[/TD]
[TD]24[/TD]
[TD]54[/TD]
[TD]654[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]104[/TD]
[TD]29[/TD]
[TD]40[/TD]
[TD]677[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]150[/TD]
[TD]40[/TD]
[TD]90[/TD]
[TD]700[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]155[/TD]
[TD]42[/TD]
[TD]88[/TD]
[TD]721[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]210[/TD]
[TD]80[/TD]
[TD]200[/TD]
[TD]900[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

Hi, not sure if this will fit your query.
Given in Sheet1 cell A1:
<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { text-align: center; vertical-align: middle; white-space: normal; }</style> [TABLE="width: 325"]
<colgroup><col style="width:65pt" span="5" width="65"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 65"]Condition[/TD]
[TD="class: xl63, width: 65"]Data 1[/TD]
[TD="class: xl63, width: 65"]Data2[/TD]
[TD="class: xl63, width: 65"]Data 3[/TD]
[TD="class: xl63, width: 65"]Data 4[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 65"]1[/TD]
[TD="class: xl63, width: 65"]0[/TD]
[TD="class: xl63, width: 65"]0[/TD]
[TD="class: xl63, width: 65"]0[/TD]
[TD="class: xl63, width: 65"]0[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 65"]1[/TD]
[TD="class: xl63, width: 65"]0[/TD]
[TD="class: xl63, width: 65"]0[/TD]
[TD="class: xl63, width: 65"]0[/TD]
[TD="class: xl63, width: 65"]0[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 65"]2[/TD]
[TD="class: xl63, width: 65"]100[/TD]
[TD="class: xl63, width: 65"]24[/TD]
[TD="class: xl63, width: 65"]54[/TD]
[TD="class: xl63, width: 65"]654[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 65"]2[/TD]
[TD="class: xl63, width: 65"]104[/TD]
[TD="class: xl63, width: 65"]29[/TD]
[TD="class: xl63, width: 65"]40[/TD]
[TD="class: xl63, width: 65"]677[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 65"]3[/TD]
[TD="class: xl63, width: 65"]0[/TD]
[TD="class: xl63, width: 65"]0[/TD]
[TD="class: xl63, width: 65"]0[/TD]
[TD="class: xl63, width: 65"]0[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 65"]3[/TD]
[TD="class: xl63, width: 65"]0[/TD]
[TD="class: xl63, width: 65"]0[/TD]
[TD="class: xl63, width: 65"]0[/TD]
[TD="class: xl63, width: 65"]0[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 65"]4[/TD]
[TD="class: xl63, width: 65"]150[/TD]
[TD="class: xl63, width: 65"]40[/TD]
[TD="class: xl63, width: 65"]90[/TD]
[TD="class: xl63, width: 65"]700[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 65"]4[/TD]
[TD="class: xl63, width: 65"]155[/TD]
[TD="class: xl63, width: 65"]42[/TD]
[TD="class: xl63, width: 65"]88[/TD]
[TD="class: xl63, width: 65"]721[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 65"]5[/TD]
[TD="class: xl63, width: 65"]0[/TD]
[TD="class: xl63, width: 65"]0[/TD]
[TD="class: xl63, width: 65"]0[/TD]
[TD="class: xl63, width: 65"]0[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 65"]5[/TD]
[TD="class: xl63, width: 65"]0[/TD]
[TD="class: xl63, width: 65"]0[/TD]
[TD="class: xl63, width: 65"]0[/TD]
[TD="class: xl63, width: 65"]0[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 65"]6[/TD]
[TD="class: xl63, width: 65"]210[/TD]
[TD="class: xl63, width: 65"]80[/TD]
[TD="class: xl63, width: 65"]200[/TD]
[TD="class: xl63, width: 65"]900[/TD]
[/TR]
</tbody>[/TABLE]

In Sheet2 cell A1:
<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }</style> [TABLE="width: 325"]
<colgroup><col style="width:65pt" span="5" width="65"> </colgroup><tbody>[TR]
[TD="width: 65"]condition[/TD]
[TD="width: 65, align: right"]4[/TD]
[TD="width: 65"][/TD]
[TD="width: 65"][/TD]
[TD="width: 65"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]700[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]155[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]88[/TD]
[TD="align: right"]721[/TD]
[/TR]
</tbody>[/TABLE]

Formula in B2 is =IFERROR(INDEX(Sheet1!B$2:B$12,SMALL(IF($B$1=Sheet1!$A$2:$A$12,ROW(Sheet1!$A$2:$A$12)-MIN(ROW(Sheet1!$A$2:$A$12))+1,""),ROW(Sheet1!A1))),"") Ctrl + Shift + Enter, not just enter on a PC or Command + Return on a MAC.
Copied right till Column E and down till needed.

Change value in B1 to fit your required condition.

Would that be close to what you need?
 
Upvote 0
Yes that's exactly what I was looking for. Thank you so much for your help! You've saved me hours of work. :biggrin:
 
Upvote 0
NucEng said:
Yes that's exactly what I was looking for. Thank you so much for your help! You've saved me hours of work. :biggrin:
Click to expand...

Most welcome.
Glad it worked for you, and thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
VBA. How to get data from Outlook message by a specific subject and time to Excel file.
Replies
3
Views
776
John_w
J
A
  • Solved
Extracting Data from a table
Replies
5
Views
361
JamesCanale
JamesCanale
netspeedz
  • Solved
Extracting Data From Kitco.com [Using VBA]
Replies
6
Views
1K
netspeedz
netspeedz
A
  • Question Question
Excel data from one sheet, copy cells from row i'm working on to excel 'form' based on cell value
Replies
2
Views
510
asiuol84
A
K
  • Solved
Macro to extract data into seperate workbooks
2
Replies
11
Views
586
Alex Blakenburg
A

Forum statistics

Threads
1,223,227
Messages
6,170,849
Members
452,361
Latest member
d3ad3y3

Share this page

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