SOLVED

Request Help. Removing Certain Data

%3CLINGO-SUB%20id%3D%22lingo-sub-1311156%22%20slang%3D%22en-US%22%3ERequest%20Help.%20Removing%20Certain%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1311156%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EI%20have%2012%2C000%20rows%20of%20data.%20I'm%20trying%20to%20get%20stats%20for%2012%20people.%20Each%20person%20has%20numerous%20payments%20under%20the%20same%20name%20(claim%20%23)%3B%20and%20multiple%20claims%20per%20person.%20I'm%20trying%20to%20consolidate%20the%20payment%20information%20with%20only%201%20claim%20per%20row%2C%20with%20that%20overall%20payment%20total%20for%20each%20claim.%20Instead%20of%20having%20multiple%20rows%20of%20the%20same%20claim%3B%20and%20each%20individual%20payment.%20Please%20see%20the%20pictures%20for%20reference.%26nbsp%3B%3C%2FP%3E%3CP%3EExcel%20has%20to%20have%20something%20to%20make%20this%20easier.%20Thank%20you.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1311156%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1311196%22%20slang%3D%22en-US%22%3ERe%3A%20Request%20Help.%20Removing%20Certain%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1311196%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F623534%22%20target%3D%22_blank%22%3E%40pw3603%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPivot%20Table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMove%20all%20fields%20(Total%20amt%20excepted)%20to%20the%20Rows.%20Move%20total%20amount%20field%20to%20the%20values.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EChange%20pivot%20table%20report%20layout%20to%20tabular.%26nbsp%3B%20Turn%20off%20all%20subtotals.%26nbsp%3B%20Grand%20totals%20may%20be%20removed%2C%20if%20desired.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1311310%22%20slang%3D%22en-US%22%3ERe%3A%20Request%20Help.%20Removing%20Certain%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1311310%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F428790%22%20target%3D%22_blank%22%3E%40Patrick2788%3C%2FA%3E%26nbsp%3BThanks%20for%20the%20advice.%20I%20did%20as%20you%20suggested.%20The%20challenge%20I%20have%20when%20creating%20a%20pivot%20table%20with%20the%20current%20data%20set%2C%20is%20it%20gives%20me%20data%20I%20don't%20want%20reflected%20(the%20count%20is%20too%20high%20because%20of%20duplicate%20claim%20%23's).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%2C%20when%20I%20create%20a%20pivot%20and%20have%20%22count%20as%20claim%20number%22%20as%20a%20column%20it's%20including%20all%20the%20duplicate%20claims%20with%20the%20same%20name.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20pivot%20I%20create%20has%20numbers%20I%20don't%20want.%20I%20need%20a%20way%20to%20remove%20duplicates%20(not%20the%20rows%20just%20the%20text%20in%20the%20field.%26nbsp%3BThe%20pictures%20I%20attached%20illustrate%20this.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1312588%22%20slang%3D%22en-US%22%3ERe%3A%20Request%20Help.%20Removing%20Certain%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1312588%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F623534%22%20target%3D%22_blank%22%3E%40pw3603%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDo%20you%20have%20a%20sample%20workbook%20you%20can%20share%3F%26nbsp%3B%20Even%20if%20the%20data%20is%20dummy'd%20out%20it%20will%20be%20helpful.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1313359%22%20slang%3D%22en-US%22%3ERe%3A%20Request%20Help.%20Removing%20Certain%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1313359%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F428790%22%20target%3D%22_blank%22%3E%40Patrick2788%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20attached%20it.%26nbsp%3B%20Thank%20you%20for%20taking%20a%20look!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1313459%22%20slang%3D%22en-US%22%3ERe%3A%20Request%20Help.%20Removing%20Certain%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1313459%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F623534%22%20target%3D%22_blank%22%3E%40pw3603%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20should%20do%20it.%20Since%20your%20table%20exists%20in%20the%20data%20model%20it%20gives%20you%20access%20to%20summarize%20the%20Claim%20by%20'Distinct%20Count'.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1313479%22%20slang%3D%22en-US%22%3ERe%3A%20Request%20Help.%20Removing%20Certain%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1313479%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F428790%22%20target%3D%22_blank%22%3E%40Patrick2788%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20did%20it!!!%20Figures%20it%20was%20not%20some%20fancy%20formula.%20You%20made%20this%20way%20more%20effective.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1313483%22%20slang%3D%22en-US%22%3ERe%3A%20Request%20Help.%20Removing%20Certain%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1313483%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F623534%22%20target%3D%22_blank%22%3E%40pw3603%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou're%20welcome.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1316173%22%20slang%3D%22en-US%22%3ERe%3A%20Request%20Help.%20Removing%20Certain%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1316173%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F428790%22%20target%3D%22_blank%22%3E%40Patrick2788%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHello.%20Any%20chance%20I%20can%20ask%20a%20follow%20up%20question%3F%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

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. 

8 Replies
Highlighted

@pw3603 

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.

Highlighted

@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. 

Highlighted

@pw3603 

Do you have a sample workbook you can share?  Even if the data is dummy'd out it will be helpful.

Highlighted

@Patrick2788 

 

I attached it.  Thank you for taking a look!

Highlighted
Best Response confirmed by pw3603 (Occasional Contributor)
Solution

@pw3603 

This should do it. Since your table exists in the data model it gives you access to summarize the Claim by 'Distinct Count'.

 

Highlighted

@Patrick2788

 

That did it!!! Figures it was not some fancy formula. You made this way more effective.

 

Thank you!

Highlighted

@pw3603 

You're welcome.

Highlighted

@Patrick2788

 

Hello. Any chance I can ask a follow up question?