Forum Discussion
Issues carrying table/data over to different sheet with matching.
Filter looked like a helpful tool as I looked into it, but unfortunately, I don't have that feature with my Excel. Is there a formula combination that can be used as a workaround? I was trying to use a combination of IFERROR, INDEX, and AGGREGATE to achieve the same thing.
You might have an easier time if you use a PivotTable for this. If you can share a file that has an example of the data, it would make it easier to help.
To get started, do these steps:
1. Select a cell in your data range, then click Insert > PivotTable.
2. Insert it onto a new worksheet.
3. After inserting it, in the Field List pane, you can drag all the fields you want to see into the Rows area.
4. Then choose Insert > Slicer and pick "Scheme".
Then you can just click a button to filter it as desired. It might look something like this, but I've put it on the same sheet just to make it simpler to see.
Ok, I was able to get the pivot table together, but it is forcing a formula in the data columns instead of just listing the issue. I think it may be due to the setup of my Set column, which describes the Set # but the number of sets at to complete for each prescribed rep, load, etc. (I posted the data below):
Scheme Name Sets1 Rep1 Load1 Tempo1 Rest1 Reps Achieved1 OBA_Strength_1_Primary 1 5 0.75 30x1 60-120s 0 OBA_Strength_1_Primary 2 3 80%-85% 30x1 90-120s 0 OBA_Strength_1_Primary 2 1-2 85%-90% 20x1 120-180s 0 OBA_Strength_1_Primary 1 RM Test 0 0 0 0 OBA_Strength_1_Assisting 1 15 0.55 31x1 90s 0 OBA_Strength_1_Assisting 1 10 0.65 31x1 90s 0 OBA_Strength_1_Assisting 0 0 0 0 0 0 As a result, I'm getting this on my pivot table:
Row Labels Sum of Sets1, Count of Rep1, Count of Load1, Count of Tempo1, Count of Rest1, Sum of Reps Achieved
OBA_Strength_1_Assisting 2 3 3 3 3 0 OBA_Strength_1_Primary 6 4 4 4 4 0 Is there a way to adjust this without having to # each Set as 1, 2, 3, 4, 5?
tylrp910 For this purpose, you don't need to have any of your fields in the Values area of the Field List pane for the PivotTable.
Do the following:
1. Drag the fields from the Values are to the Rows area. The Values area should be empty since you don't need to calculate anything.
2. Go to the Design tab of the ribbon, and choose Subtotals > Do not show.
3. Choose Grand Totals > Off for Rows and Columns.
4. On the PivotTable Analyze tab, click the "+/- Buttons" button to hide all the buttons.
5. Adjust the alignment of the cells so it looks the way you want.
Also, you can attach files to these discussions by clicking on the "Open full text editor" link.