Selecting multiple cells based on the value of an adjacent cell

m0nkeyb0y66

New Member
Joined
Aug 4, 2022
Messages
1
Office Version
  1. 365
Platform
  1. Windows
I am collecting sensor data from an Arduino project, and it saves the data to a text file. Each second 16 data points are collected. Three seconds of that data looks like the following with the spaces separating the data for each second it was recorded:

29.02
84.23
55.63
0.12
25.11
971
372.29
77.10
770.70
-92.70
0.06
0.02
0.05
-0.06
0.12
9.78

29.02
84.23
55.63
0.12
25.11
971
372.29
77.10
765.90
-93.30
0.07
0.02
0.05
-0.07
0.11
9.79

29.02
84.23
55.63
0.12
25.11
971
372.29
81.90
768.30
-92.70
0.06
0.00
0.05
-0.08
0.12
9.81

I have copied all of this data into an Excel spreadsheet and added a column with headings for each data point. I now have two columns that look like the following:

Temp ℃: 29.02
Temp ℉: 84.23
Humidity %: 55.63
Ultraviolet intensity mw/cm2: 0.12
LuminousIntensity lx: 25.11
Atmospheric pressure hpa: 971
Altitude m: 372.29
Mag X uT: 77.1
Mag Y uT:: 770.7
Mag Z uT: -92.7
Gyryo X g: 0.06
Gyryo Y g: 0.02
Gyryo Z g: 0.05
Accel X m/s^2 -0.06
Accel Y m/s^2 0.12
Accel Z m/s^2 9.78
--------------------------------------
Temp ℃: 29.02
Temp ℉: 84.23
Humidity %: 55.63
Ultraviolet intensity mw/cm2: 0.12
LuminousIntensity lx: 25.11
Atmospheric pressure hpa: 971
Altitude m: 372.29
Mag X uT: 77.1
Mag Y uT:: 765.9
Mag Z uT: -93.3
Gyryo X g: 0.07
Gyryo Y g: 0.02
Gyryo Z g: 0.05
Accel X m/s^2 -0.07
Accel Y m/s^2 0.11
Accel Z m/s^2 9.79
--------------------------------------
Temp ℃: 29.02
Temp ℉: 84.23
Humidity %: 55.63
Ultraviolet intensity mw/cm2: 0.12
LuminousIntensity lx: 25.11
Atmospheric pressure hpa: 971
Altitude m: 372.29
Mag X uT: 81.9
Mag Y uT:: 768.3
Mag Z uT: -92.7
Gyryo X g: 0.06
Gyryo Y g: 0
Gyryo Z g: 0.05
Accel X m/s^2 -0.08
Accel Y m/s^2 0.12
Accel Z m/s^2 9.81
--------------------------------------

I'd like to be able to separate the data points based on the heading that goes with them. I want to get all of the readings for Mag Z uT and be able to paste them to a separate sheet or column so that I can select them all and create a graph of the change over time. Is there a way to do this quickly? Can I select numbers in column B based on what it says in the adjacent cell in column A? For example, only select cells in column B if the adjacent cell in column A says Mag Z uT.

Book1
AB
1Temp ℃:29.02
2Temp ℉:84.23
3Humidity %:55.63
4Ultraviolet intensity mw/cm2:0.12
5LuminousIntensity lx:25.11
6Atmospheric pressure hpa:971
7Altitude m:372.29
8Mag X uT:77.1
9Mag Y uT::770.7
10Mag Z uT:-92.7
11Gyryo X g:0.06
12Gyryo Y g:0.02
13Gyryo Z g:0.05
14Accel X m/s^2-0.06
15Accel Y m/s^20.12
16Accel Z m/s^29.78
17--------------------------------------
18Temp ℃:29.02
19Temp ℉:84.23
20Humidity %:55.63
21Ultraviolet intensity mw/cm2:0.12
22LuminousIntensity lx:25.11
23Atmospheric pressure hpa:971
24Altitude m:372.29
25Mag X uT:77.1
26Mag Y uT::765.9
27Mag Z uT:-93.3
28Gyryo X g:0.07
29Gyryo Y g:0.02
30Gyryo Z g:0.05
31Accel X m/s^2-0.07
32Accel Y m/s^20.11
33Accel Z m/s^29.79
34--------------------------------------
35Temp ℃:29.02
36Temp ℉:84.23
37Humidity %:55.63
38Ultraviolet intensity mw/cm2:0.12
39LuminousIntensity lx:25.11
40Atmospheric pressure hpa:971
41Altitude m:372.29
42Mag X uT:81.9
43Mag Y uT::768.3
44Mag Z uT:-92.7
45Gyryo X g:0.06
46Gyryo Y g:0
47Gyryo Z g:0.05
48Accel X m/s^2-0.08
49Accel Y m/s^20.12
50Accel Z m/s^29.81
51--------------------------------------
Sheet1
Cell Formulas
RangeFormula
A18:A51A18=A1
Named Ranges
NameRefers ToCells
Print_Titles=Sheet1!$A:$AA18
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Let's say that you queue your data in column A & B

Now in D2 insert the formula
Excel Formula:
=LET(myTABL,A2:B10000,FilterBy,"Mag Z uT:",FILTER(myTABL,INDEX(myTABL,0,1)=FilterBy))
This will return all the rows where col A is equal to "Mag Z uT:" (case insensitive)
If you need other data, replicate the same formula but changing the value of the label FilterBy

This formula works with Office 365 (and other versions that handle Dynamic Arrays)

But you might also use directly an Autofilter on the column A
 
Upvote 0

Forum statistics

Threads
1,223,970
Messages
6,175,707
Members
452,667
Latest member
vanessavalentino83

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