Unique values based on criteria

Chaostheory72

New Member
Joined
Oct 3, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Going to do my best to describe my issue. I have a spread sheet that tracks welds we are making on a project. The project will have welds labeled 1-1000. These welds will either be accepted or rejected. If the weld is rejected it will have a reason why listed in another cell. I have everything setup to auto populate and calculate to a main summary page for easy to read results for the end user. With that being said is there a formula that would allow all the rejected said welds to be copied to the summary page. I only want it to copy and display the rejected numbers on the summary page. But if the criteria is met and say weld 1 is rejected I would like the summary page to display "1" and in the adjacent cell to copy the text for why the weld was rejected. I have gotten this formula to work for copying all the said rejected welds to the summary page. =INDEX($C$3:$C$10,MATCH(0,COUNTIF($G$2:G2,$C$3:$C$10)+($B$3:$B$10<>$E$3),0))
I am lost on how to get the summary page then to compare the results to other page and copy over the reason why said weld was rejected.

Attached is my example which hopefully helps more than my ramble above. Any help would be greatly appreciated.
 

Attachments

  • unique value sample.png
    unique value sample.png
    33.6 KB · Views: 10

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Something like this?

FactSheet_Data_New (19).xlsx
BCDEFGHIJKLMN
2A/RItemColumn1CategoryColumn2ReasonUVReasonUVReason
3aGPS-G-0001rejectGPS-G-0003crackGPS-G-0003crack
4aGPS-G-0002GPS-G-0005bad crackGPS-G-0005bad crack
5rGPS-G-0003crackGPS-G-0006small crackGPS-G-0006small crack
6aGPS-G-0004GPS-G-0008uglyGPS-G-0008ugly
7rGPS-G-0005bad crack  
8rGPS-G-0006small crack  
9aGPS-G-0007  
10rGPS-G-0008ugly  
Sheet1
Cell Formulas
RangeFormula
M3:N6M3=LET(f,FILTER(B3:G10,G3:G10<>""),INDEX(f,SEQUENCE(ROWS(f)),{2,6}))
I3:I10I3=IFERROR(INDEX($C$3:$C$10,AGGREGATE(15,6,(ROW($C$3:$C$10)-ROW($C$2))/($G$3:$G$10<>""),ROWS($A$1:A1))),"")
K3:K10K3=IFERROR(INDEX($G$3:$G$10,AGGREGATE(15,6,(ROW($C$3:$C$10)-ROW($C$2))/($G$3:$G$10<>""),ROWS($A$1:A1))),"")
Dynamic array formulas.
 
Upvote 0
Thank you very much. I think that will do exactly what I need. Thank you again for the help and quick reply.
 
Upvote 0

It looks like this will work for me to an extent. The issue im running into is it looks to be baser purely off of the data in column "G". But lets say its a good weld but I need to add a comment like this weld was great or this weld was not used. The weld was accepted but since there is a comment it would auto-populate the weld over because it had a comment. I would like to refrain from having a good and a bad comment section. I guess I need it to look at the value entered in column "B" and if its a rejected weld then copy the comments over. If the value in B is accepted then the comments stay on the main data page but don't copy over into the list of rejected welds. Is this possible or am I asking to much. Thank you in advance for any help with this.
 
Upvote 0
Maybe:
Book2
BCDEFGHIJKL
1A/RItemCategoryReasonUnique ValueReason
2AcceptGPS-G-0001rejectGPS-G-0003crack
3AcceptGPS-G-0002GPS-G-0005bad crack
4RejectGPS-G-0003crackGPS-G-0006small crack
5AcceptGPS-G-0004Good WeldGPS-G-0008ugly
6RejectGPS-G-0005bad crackGPS-G-0003Still Bad
7RejectGPS-G-0006small crack
8AcceptGPS-G-0007
9RejectGPS-G-0008ugly
10RejectGPS-G-0003Still Bad
Sheet1
Cell Formulas
RangeFormula
J2:J6J2=FILTER($C$2:$C$10,($B$2:$B$10="Reject"),"")
L2:L6L2=FILTER($G$2:$G$10,($B$2:$B$10="Reject"),"")
Dynamic array formulas.
 
Upvote 0
Solution

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

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