Generating a data summary output sheet from a from larger data input sheet

M

mfitz51

New Member
Joined
Jun 6, 2024
Messages
5
Office Version
  1. 365
Platform
  1. Windows
  2. Web
I'm collecting harvesting data and need a summary of the [breeding line + gene code] and each of those combinations' harvesting data.

Harvest data meaning:
1. their # of samples collected
2. how many samples have actually generated a yield (counting all non zeros, many don't yield anything)
3. of those samples that generated yield, how much yield total was generated for their breeding line + gene code

The two screenshots I included are examples of the input data I collect, and then the desired output data I would like the automate.
Not sure if code is able to do this, or if anyone has just some helpful tips. Currently I'm just filtering as a table and summing them manually, which is error prone.

Thank you so much for any help!!
 

Attachments

  • Screenshot 2024-06-12 162653.png
    Screenshot 2024-06-12 162653.png
    15.5 KB · Views: 9
  • Screenshot 2024-06-12 162716.png
    Screenshot 2024-06-12 162716.png
    10.6 KB · Views: 9

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
Here's a start. I only did formulas for the counts (yellow highlights), but other formulas can be done for the other data if needed.
Book1
ABCDEFG
1Date #1Date #2Breeding LineGene#Samples Screened#Samples Yielding (non Zeros)Total # Harvested
26/11/20246/12/2024LineAGene00110518
36/11/20246/12/2024LineBGene001414
46/11/20246/12/2024LineCGene002600
Sheet7
Cell Formulas
RangeFormula
E2:E4E2=COUNTIFS(Sheet6!$C$2:$C$21,C2,Sheet6!$D$2:$D$21,D2)
F2:F4F2=COUNTIFS(Sheet6!$C$2:$C$21,C2,Sheet6!$D$2:$D$21,D2,Sheet6!$E$2:$E$21,">0")
G2:G4G2=SUMIFS(Sheet6!$E$2:$E$21,Sheet6!$C$2:$C$21,C2,Sheet6!$D$2:$D$21,D2)


Book1
ABCDE
1Sample IDDate#1Breeding LineGene#Harvested
2ID0016/11/2024LineAGene0010
3ID0026/11/2024LineAGene0014
4ID0036/11/2024LineAGene0014
5ID0046/11/2024LineAGene0010
6ID0056/11/2024LineAGene0010
7ID0066/11/2024LineAGene0011
8ID0076/11/2024LineAGene0013
9ID0086/11/2024LineAGene0010
10ID0096/11/2024LineAGene0016
11ID0106/11/2024LineAGene0010
12ID0116/11/2024LineBGene0010
13ID0126/11/2024LineBGene0014
14ID0136/11/2024LineBGene0010
15ID0146/11/2024LineBGene0010
16ID0156/11/2024LineCGene0020
17ID0166/11/2024LineCGene0020
18ID0176/11/2024LineCGene0020
19ID0186/11/2024LineCGene0020
20ID0196/11/2024LineCGene0020
21ID0206/11/2024LineCGene0020
Sheet6
 
Upvote 1
The counting works great, thanks for that tip!

Is there a way to grab the breeding line & gene pairings? Currently I just apply a filter to the table and sort to isolate each breeding line. They usually have 1-10 different gene pairings, so I have to copy each gene to make the multiple pairings (ex: LineA + Gene001, LineA + Gene002, LineA + Gene003, etc.)

Ideally I'd like a sheet that can list out all the breedings lines and their associated gene pairings. Then apply the CountIFS and SumIFS functions to that. Is that possible or is my current method the most efficient?
 
Upvote 0
Yes, you can use UNIQUE() like this:
Book1
ABCDEFGH
1Sample IDDate#1Breeding LineGene#Harvested
2ID0016/11/2024LineAGene0010LineAGene001
3ID0026/11/2024LineAGene0014LineBGene001
4ID0036/11/2024LineAGene0014LineCGene002
5ID0046/11/2024LineAGene0010
6ID0056/11/2024LineAGene0010
7ID0066/11/2024LineAGene0011
8ID0076/11/2024LineAGene0013
9ID0086/11/2024LineAGene0010
10ID0096/11/2024LineAGene0016
11ID0106/11/2024LineAGene0010
12ID0116/11/2024LineBGene0010
13ID0126/11/2024LineBGene0014
14ID0136/11/2024LineBGene0010
15ID0146/11/2024LineBGene0010
16ID0156/11/2024LineCGene0020
17ID0166/11/2024LineCGene0020
18ID0176/11/2024LineCGene0020
19ID0186/11/2024LineCGene0020
20ID0196/11/2024LineCGene0020
21ID0206/11/2024LineCGene0020
Sheet6
Cell Formulas
RangeFormula
G2:H4G2=UNIQUE(C2:D21)
Dynamic array formulas.
 
Upvote 1
Solution
You must log in or register to reply here.

Similar threads

E
  • Solved
All Unique Combinations from 5 Columns of Data + Customizations - Excel 365
Replies
6
Views
241
Excel Jason
E
A
  • Question Question
Take screenshots & simultaneously capture the link, place it into an Excel file which is linked to a FileMaker/Claris db.
Replies
3
Views
425
Dan_W
D
Q
  • Question Question
Macro needed to insert 4 rows in table, copy data from above, copy data from another sheet and paste special (transpose values) to those new rows...re
Replies
1
Views
402
sijpie
sijpie
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
545
asiuol84
A
S
  • Solved
Transfer of data range (multiple cells) from one sheet to another sheet automatically at the next blank row.
Replies
3
Views
291
Alex Blakenburg
A

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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