Excel extracting multiple matching rows

Multi tool use
Multi tool use


Excel extracting multiple matching rows



I have a matrix of data which has the records of diferent products ( by serial number) and a category (name of the brand) which has several products.
The data set consists of multiple columns for each product and I want to compare the different categories with a box-Whisher plot some of the settings.



enter image description here



For example, I want to compare results from Data1 for the two diferent motor brands.



My question is how can I dinamically select the range of each "brand"?



I would search for the name of the motor I would like to plot in the leftmost column and retrive the rows with the column content I wish to plot. Though, I can not figure out how I could do this in excel.



Thanks!



EDIT:



The idea is to automatically update each series value of the box plot, where each series name is the brand of the motor. I have 10 different brands of motors, where they could have different number of serial numbers. Therefore, I am looking for a formula/VBA that could for each motor type " see how many serial numbers exist(rows) of that motor and select the values of one data type (e.g. Data1)to the series value of the box plot.
I do not know if I explained my problem clearly..





I would recommend being more specific with your desired output. There are many ways of achieving what you want it to do, but some are more efficient than others, depending on how you wish to manipulate with the data afterwards. Ideally create a mockup of an expected output of the data you provided in your picture
– Rawrplus
Jul 3 at 9:16






The idea is to automatically update each series value of the box plot, where each series name is the brand of the motor. I have 10 different brands of motors, where they could have different number of serial numbers. Therefore, I am looking for a formula/VBA that could for each motor type " see how many serial numbers exist(rows) of that motor and select the values of one data type (e.g. Data1)to the series value of the box plot. I do not know if I explained my problem clearly..
– Francisco Costa
Jul 3 at 10:57




1 Answer
1



The range of Rolls Royce in columns A:G will be,


=index(a:a, match("Rolls Royce", a:a, 0)):index(g:g, aggregate(14, 6, row($2:$9999)/(a$2:a$9999="Rolls Royce"), 1))



This range can be proved with the sum of column D (data1).


=sum(index(d:d, match("Rolls Royce", a:a, 0)):index(d:d, aggregate(14, 6, row($2:$9999)/(a$2:a$9999="Rolls Royce"), 1)))






By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

XrfcKN,nX5K3dseGW1yJNN3tffVx
-
1GFTzuzqjvq,SK4DBzF 0zQwFW bsPE6cSPKvvPysyH5II PWFX76ki,02GX0 6uckRJcqj YqWfOjV,mc7LjPf2xV40D G N QjNw26Eh,p7mr

Popular posts from this blog

PHP contact form sending but not receiving emails

PHP contact form sending but not receiving emails I have created a contact form using PHP to send the data. The form seems to send fine as there are no errors and success message appears, however I am not receving the emails into the designated inbox. I am using PHPMailer as I originally tried just using the php 'mail' command which I now understand is a bit hit and miss. I cannot see why I would not be receving the emails so I would be very grateful for any help that could be given. I'm quite new to PHP so please be patient with me :) <?php use PHPMailerPHPMailerPHPMailer; use PHPMailerPHPMailerException; $msg = ""; if (isset($_POST['submit'])) { require 'phpmailer/src/Exception.php'; require 'phpmailer/src/PHPMailer.php'; require 'phpmailer/src/SMTP.php'; function sendemail ($to, $from, $body) { $mail = new PHPMailer(true); $mail->setFrom($from); $mail->addAddress($to); $ma...
Read more

Do graphics cards have individual ID by which single devices can be distinguished?

Do graphics cards have individual ID by which single devices can be distinguished? I have several graphics cards of the same manufacturer and same model and I want to distinguish them not only by the pcie socket they reside in but by some individual value I can read from the hardware. Is there such value and if so what would it be called and how could I access it? Right now I'm using modern nvidia cards (10xx series) and nvidia inspector.but I'd prefer if there was any value that was also applicable to amd. I have programming and scripting knowledge. I am running Windows 10 1 Answer 1 This is a powershell example to pull information from video cards. The DeviceID is supposed to be unique but only if the manufacturer supports Windows Display Driver Model (WDDM). (I copied this directly from docs.microsoft.com) $strComputer = "." $colItems = get-wmiobject -class "Win32_VideoC...
Read more

Create weekly swift ios local notifications

Create weekly swift ios local notifications I can create daily notifications but not weekly ones using swift 3 ios 10 User Notifications framework. Daily works fine but when I add in the .weekday parameter it doesn't send me a notification. The code is: for i in 1 ... 7 { let center = UNUserNotificationCenter.current(); let content = UNMutableNotificationContent(); content.title = "Title"; content.body = "content"; content.sound = UNNotificationSound.default(); var dateComponents = DateComponents(); dateComponents.weekday = i; // hours is 00 to 11 in string format if (daynight == "am") { dateComponents.hour = Int(hours); } else { dateComponents.hour = Int(hours)! + 12; } // mins is 00 to 59 in string format dateCom...
Read more