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 ?
1
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
0
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
0
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
0
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
0
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
0
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 .
1
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)
0

Similar discussions

Unlock safe folder without losing any data
Mintu -
HelpiOS -

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

2 responses
AMD data change update new data to DMI
Luis -
dwebb -

1 response
Hosted app data will not clear
Woody_3336 -
woody_3336 -

7 responses