Apr 15 2020 04:13 PM - edited Apr 15 2020 04:16 PM
Hello,
I have 12,000 rows of data. I'm trying to get stats for 12 people. Each person has numerous payments under the same name (claim #); and multiple claims per person. I'm trying to consolidate the payment information with only 1 claim per row, with that overall payment total for each claim. Instead of having multiple rows of the same claim; and each individual payment.
Please see the pictures for reference.
Excel has to have something to make this easier.
Thank you.
Apr 15 2020 04:37 PM
Pivot Table.
Move all fields (Total amt excepted) to the Rows. Move total amount field to the values.
Change pivot table report layout to tabular. Turn off all subtotals. Grand totals may be removed, if desired.
Apr 15 2020 05:54 PM
@Patrick2788 Thanks for the advice. I did as you suggested. The challenge I have when creating a pivot table with the current data set, is it gives me data I don't want reflected (the count is too high because of duplicate claim #'s).
For example, when I create a pivot and have "count as claim number" as a column it's including all the duplicate claims with the same name.
Any pivot I create has numbers I don't want. I need a way to remove duplicates (not the rows just the text in the field. The pictures I attached illustrate this.
Apr 16 2020 05:32 AM
Do you have a sample workbook you can share? Even if the data is dummy'd out it will be helpful.
Apr 16 2020 08:52 AM
Apr 16 2020 09:36 AM - edited Apr 16 2020 09:36 AM
SolutionThis should do it. Since your table exists in the data model it gives you access to summarize the Claim by 'Distinct Count'.
Apr 16 2020 09:45 AM
That did it!!! Figures it was not some fancy formula. You made this way more effective.
Thank you!
Apr 17 2020 08:48 AM
Apr 16 2020 09:36 AM - edited Apr 16 2020 09:36 AM
SolutionThis should do it. Since your table exists in the data model it gives you access to summarize the Claim by 'Distinct Count'.