Need to combine 2 rows of data into one line while summing their contents

Basically, we have 2 tablets tallying data and writing to the same .csv file. That csv file is then copied into a front page of a much larger excel worksheet where the tallied data is sorted and used for a variety of reasons. This works perfectly using index and match when only 1 tablet is being used, however, when both tablets are writing to the same csv at the same time it creates 2 lines instead of 1 line.

For example 1 tablet might look like this:

Lot 45689 4 6 8 9 1 5

2 tablets looks like this:

Lot 45689 2 3 1 8 1 4
Lot 45689 2 3 7 1 0 1


for the tallies, i would imagine a =SUMIF function might suffice, but that doesn't solve my problem of there being 2 lines with the same lot #.

I am only dealing with this issue from the excel side, and have no control over the tablet functionality or the CSV file which further limits me.

Any help would be greatly appreciated. Thanks!

Wanted to add that the Lot #s change daily and often so with my understanding of pivot tables this rules them out as a viable option.

kyleh186, welcome to the forum. Can you please upload a small sample workbook (with anything confidential removed)? INDEX/MATCH will indeed only return the first match it finds, but multiple rows with the same Lot # shouldn't be a problem for SUMIF - that's what it's for.

So once the tablets populate the data to the csv, you want a way to convert the 2 lines into 1 line?

How much such lot numbers would there be in the file at one point of time? Just 1 set of many?

Using your spreadsheet this is the formula I wrote and it works.
=SUMIF('How the CSV file looks'!$A:$A,'How I need the data formatted'!$A4,'How the CSV file looks'!B:B)
It handles multiple versions of the lot # as well as single instances.

Ok, see what you need. The process i'd probably use is likely less "elegant" than others would use. I would likely use an advanced filter highlighting column A then copy to another location and check unique records only. THIS however will only let you copy them into the same worksheet. Then after doing that I'd cut and paste them into column A in the second worksheet then do the sumif after that.
I'm sure there are more ways to do it that would involve fewer steps than mine. Maybe someone else can answer that one. Good luck and i'll watch for another reply.

Ok, I found this formula in another post that may help. Here is the link...
http://www.excelforum.com/excel-form...-quantity.html
Fotis1991 posted it and it works (w/a minor change I made)
Re: How to remove duplicates & add up the quantity?
Another way is using this formula in another column(let's say E). So in E2 and copy down.

=LOOKUP(REPT("Z",5),CHOOSE({1,2},"",INDEX(A$2:A$2741,MATCH(TRUE,INDEX(ISNA(MATCH(A$2:A$2741,E$1:E1,0)),0),0),1)))

Then in your other column use a SUMIF function to add the numbers

=SUMIF($A$2:$A$3000;E2;$B$2:$B$3000) copy down.
<><><><><>
I tested it out and it worked
I'm not an "expert" at posting so I hope this doesn't violate any forum rules.
Hope it helps.

Do you want a macro(code) solution?

If your CSV files are added together one after the other then something like this will work. If the calculation is no longer required once the data is on the How I need...page the data can be copied and paste special values in place to eliminate the formulae.