Previous Topic Next Topic

Data consolidation

Closed
Karthikeyan1207 Posts 16 Registration date Wednesday November 26, 2014 Status Member Last seen August 3, 2020 - Dec 27, 2015 at 01:33 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Jan 5, 2016 at 11:07 AM
Dears
Need your expertise advice.
I have an excel data in below format , need a formula

Column 1 has text like ABA, ABA , ABB, ABC, ABC, ABC . ( pls note the dublication)
Column 2 has numeric value
Now my requirement is : In column 1, if 1st row and 2nd row is equal , then the numeric value in column 2 needs to be added and put a consolidated number in column 3... If its not equal than same numeric of column 2 needs to be maitained in column 3 . Like wise each consecutive rows needs to be checked ...
If row 1, row 2, row 3 are same , then i need to sumup all 3 values in Column 2 and put it Column 3- row 1.
Later I will delete the duplication of Column .

My sheet contain many data, hence i cant go with pivot , need some formula ( If condition may be ) , pls help

Check below data format :

Column 1 Column 2 Columen 3 ( result )
ABA 2 3
ABA 1 1
ABB 3 3
ABC 3 8
ABC 2 5
ABC 3 3


Regards
Karthikeyan

2 responses

Answer 1 / 2
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Dec 27, 2015 at 08:15 AM
you said later you would delete the duplicate data
so ideally you would rather see data like this

Column 1 Column 2 Columen 3 ( result )
ABA 2 3
ABB 3 3
ABC 3 8

Is that so ?
RayH Posts 122 Registration date Tuesday August 31, 2010 Status Contributor Last seen June 20, 2016 26
Dec 27, 2015 at 02:46 PM
could you possibly use the subtotal feature? 

col1	col2
ABA Total	3
ABB Total	3
ABC Total	8
Grand Total	14
Karthikeyan1207 Posts 16 Registration date Wednesday November 26, 2014 Status Member Last seen August 3, 2020 1 > RayH Posts 122 Registration date Tuesday August 31, 2010 Status Contributor Last seen June 20, 2016
Dec 28, 2015 at 01:45 AM
Hi Ray
the number of line items are huge , its tedious job to to subtotal for each sets of line item, hence need some better options. pls help
RayH Posts 122 Registration date Tuesday August 31, 2010 Status Contributor Last seen June 20, 2016 26 > RayH Posts 122 Registration date Tuesday August 31, 2010 Status Contributor Last seen June 20, 2016
Dec 28, 2015 at 02:49 PM
and that's why it should be used.
"It is generally easier to create a list with subtotals by using the Subtotal command in the Outline group on the Data tab in the Excel desktop application"

https://support.microsoft.com/en-us/office/subtotal-function-7b027003-f060-4ade-9040-e478765b9939?ui=en-us&rs=en-us&ad=us
Karthikeyan1207 Posts 16 Registration date Wednesday November 26, 2014 Status Member Last seen August 3, 2020 1
Dec 28, 2015 at 01:24 AM
Hi Riz
yes, you are exactly right, result should be as below , any possibilities

ABA 2 3
ABB 3 3
ABC 3 8
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Dec 28, 2015 at 02:38 PM
Most easy solution that comes to my mind is
1. Copy column 1 to a new sheet lets say in column A
2. use Data -- Advance filter option and copy the unique value to column B of new sheet
3. delete column A from new sheet
4. Now you should be able to use Sumif to get your answer
Answer 2 / 2
Karthikeyan1207 Posts 16 Registration date Wednesday November 26, 2014 Status Member Last seen August 3, 2020 1
Dec 29, 2015 at 06:03 AM
Dears,
I tried Sumif but not getting the result in each line item , I could get the result with this formula -
A column - Data , B column - Numbers , C column result.

=IF(A3=A4,B3+C4,B3)


Then i copy paste special , remove duplication

Thanks for your timing and advice , tips, its helpful .
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jan 5, 2016 at 11:07 AM
Using SUM IF, you can get the answer

Sheet1 = sheet where your original data is, A= column 1, B=column 2
Sheet2 = sheet where unique data of column 1 is, A=unique values of column 1

=SUMIF(Sheet1!A:A,Sheet2!A2,Sheet1!B:B)

Similar discussions

How To Fix Mint Mobile Data Not Working?
sachin327 -
joshitha -

2 responses
Unlock safe folder without losing any data
Mintu -
HelpiOS -

1 response
AMD data change update new data to DMI
Luis -
dwebb -

1 response