Forum Discussion
Request Help. Removing Certain Data
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.
- Example # 1 is the current state. The red font is the duplicate data I need to delete. The black I want to keep. My goal is to consolidate each claim on one row, instead of multiples rows; and with the overall total for each claim.
- Example # 2 shows my goal after I consolidated the claims. You can see how I just need the totals in one row.
Thank you.
This should do it. Since your table exists in the data model it gives you access to summarize the Claim by 'Distinct Count'.
8 Replies
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.
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.
Do you have a sample workbook you can share? Even if the data is dummy'd out it will be helpful.
