[Closed] Excel Help please?

you could merge them & deduplicate (probably using a macro) but I bet there's a better way

nb – data comes from 2 separate system, so cant join them anywhere – therefore has to be in excel

I'm assuming based on your description that they have some common fields.

You could therefore join the data into one sheet with blanks in the relevant cells for any day that doesn't exist on a particular sheet?

You could therefore join the data into one sheet with blanks in the relevant cells for any day that doesn’t exist on a particular sheet?

Ooooh, hadn't thought of that. i will see what i can do...

cheers!

What version of excel are you on?

its on W10, so 2016...

If I've understood the question, I'd get both tabs into the same column structure, moving columns and adding blank columns, if necessary. Then paste tab2 onto tab1. Then sort by whatever fields you're interested in (by customer & date?) then use SUBTOTAL to add the fields.

Excel 2016 you say, and it has to be done in Excel? I’d do this

https://blog.jamesbayley.com/2017/04/03/create-the-union-of-two-tables-in-excel-revisited/

Edit: well, I’d sort of do that.  Now I’ve read it it shows how to append two tables of data but if you want to avoid duplication I’d merge instead being careful about the fields being used for the join

......and now I’ve read your op again I think I’d be using the append then summarising the data however you want it but I’m not clear on how you want the final data to look

evening all,

not sure i have explained this well, so i'll try again

tab1

date, customerNo, credit/debit, amount

only 1 record per day, by customer, by direction, with an associated amount

tab2
as per tab1 but amount will be different

tab2 may contain records by date,customerNo and direction and have an amount which needs to be added to the corresponding record in tabe1 - in effect tab2 may contain missing transactions which i need to add to the amount in tab1, if they actually exist in tab2. i have already ID'd the transactions in tab1 which have a corresponding transaction in tab2, i now need to work out how to add each individual amount per line in tab 2, to the corresponding individual line in tab1, thus making a new variable in tab1 (total amount) which is the sum of the two amounts...

all data in tab2 can be dropped at this point, as i now have a new total amount by date, customerNo and direction.

clear as mud?

Sounds like SUMIFS might be what you need?

Ie. Total amount = tab1 amount + sumifs (tab2 amount range, tab2 customer number range = tab1 customer number, tab2 date range = tab1 date, tab2 transaction type range = tab1 transaction type)

If I read this right...

You have data by company etcin both tabs
there is no duplicate data
You need to somethinge the totals only if company date etc match?

So for instance
Company c amount a date d
Company c amount b date d
Company c amount x date e

Becomes
Company c amount a+b date d
Company c amount x date e?

Data is all distinct?

Is this a one off or do you need to do this as an on ongoing thing?

Dangeourvrain,

yep thats it. data is all distinct.

its a one of too.

Couldn’t you merge the 2 tables and then use a pivot table based on company, date and sum the value?

How about adding a column to tab 1 with a VLOOKUP pointed at tab2 to return the amount for any entries matching your unique ID column? Then another column to add that to the existing amount. You'd probably need to nest the VLOOKUP inside an IF with an ISNA to put zero in where there's no match.

Can you just paste the second lot of data under the first lot (assuming the columns are in the same order).

Then highlight the whole lot and go to Data-> Remove Duplicates, leave all the boxes ticked and if the same set of "date, customerNo, credit/debit, amount" exists more than once then all duplicates will be removed, leaving just one line for that day, customer and amount.

Edit:

extra credit – what if there is more than 1 extra line for a customer on a specific day – how do i add more than 1 line from tab 2…

It sounds like you're in a bit of a mess tbh, I assumed these were daily totals that had in some cases been double-entered on both systems, but if they're actually records with no unique ID that could be in system 1, system 2 or both, then you're going to struggle to unpick it. Also, how do you know that something hasn't ended up record in neither system?

Could you do something involving merging the sheets, use conditional formatting to highlight duplicate IDs then filter by colour to show therefore entries from both sheets. After that it might be a manual job?

It sounds like a SUMIF, although as always with Excel questions, the devil is in the detail and the way it has been worded. Insert a new column on tab1 and for every row/entry on tab1 do a SUMIF that looks at tab2, where the criteria in the SUMIF is the unique identifier. If a record(s) exists on tab2 then it'll sum them all up. Then just add the original tab1 amount to the new amount, in the new column.

All,

I manged to get this working this morning.

As each tab had a unique ID for the records i was interested in, I merged the 2 tabs, sorted on that ID, then a wee bit of manual manipulation and the grand total was achieved!

thanks to all fro your help.

NB - in the usual manner... now you have done that, can you do... as well!

🙁