Hi,I have a dataset where the same ID appears in multiple rows, each with 1 to 3 categories. The columns range from costA to costZ, and I need to consolidate them into a single "Cost" column. My goal is to group by Category 1 according to specific criteria (1 if Category 1 < 2000, 2 if Category 1 = 2000 - 4000, 3 if Category 1 = 4000 - 6000, 4 if Category 1 = 6000 - 10000, 5 if Category 1 > 10000).
However, I want to exclude Category 1 entries that do not have Category 2 or 3 counterparts. Additionally, the time span for Categories 1 to 3 should not exceed 1 month (I have a date column for reference). It's important to note that one ID can have multiple Category 1 to 3 entries.
I've attempted to stack the data but encountered difficulties in properly grouping Category 1 with the other categories. My objective is to create a graph showing Category (x) versus Cost (y) by group.
Does anyone have any ideas or suggestions? Your assistance would be greatly appreciated. Thank you! (I've also attached the Excel file for reference.) I'm using JMP version 16.
Attached: 1 picture and Excel file
