I build my liquidity budget in excel with a query (same one as before if anyone remembers). I have made it work with a couple of formulas so that (amongst a ton of other stuff), it now says:

Customer name Amount open Date expected

Amount open is turned into 0 when the customer pays. I have a pivot table that displays the expected payments per week, in a pivot table grouped per 7 days. The problem is that it displays every week since I made this in november or so, leaving a tons of empty weeks because everything has been paid.

I would like to make the pivot so that I can set it to show all open amounts expected from before the current month as a group so I know how much a customer still owes us currently, and making the table more readable. Any ideas how I could do that? I would prefer to not manually delete data from my query table.

I might not sure I have understood this 100%, but the number you gave can never equal itself. Here is a macro that I wrote that appears to work with 198.

Edit: Forgot the C1 part.

is this place to get some excel help?

If so.. i'm not very experienced at it, and could use some advice


I am looking for a way to merge to different tables, on different spreadsheets and combine them into one; both of them have columns with unique data that matches and also have different data. I'd like to combine them into one table, with matched data combined and different data added.
one table has columns 'abcde'
the other has columns 'efg"
I'd like them to become 'abcdefg'
Thank you.

(this is for an older version of excel btw, 2000 I believe)

I think both of the last answers misinterpreted what you were asking for, but if you copy zomg's reverseNumber function into a module, you can write this in cell B1:

And this in cell A2:

Then drag-copy those formulas across through as many cells as required. Once you get near column Z if a match hasn't been found yet you'll get an error due to the numbers being too large. So you probably don't need to drag further than that, but if you do, you'll need a much more complex formula to continue calculating.

Are there any situations where there will be a value for "e" in table 1 but not table 2 or vice versa? If yes to either do you want that line included in the new table without any associated abcd or fg columns?

e will always have the same value on both tables, it is the one that matches

I think you want the answer after 105 rows because row 1 would represent the state of the robots at the beginning of week 1. So row 105 would show the results after 104 weeks.

And the formulas you would need in row 2 are:

A2:
B2:
C2:
D2:
E2:
F2:
Copied down to row 105, and then the sum of A105:F105 for the answer. I would format all cells as "Number" using 0 decimals and the 1,000 separator to make it easier to read.

If every e that exists in table 1 also exists in table 2, and every e that exists in table 2 also exists in table 1, is there a reason you can't sort both tables by column e and then copy->paste table 2 onto table 1?

ooooh that makes sense; what if there are instances when there data that exists in table 1, doesn't exist in table two? like some items will only entered on one table

this will be really helpful. ty very much

I don't know, that's why I was trying to confirm they would match up. If you're not sure, you could sort both and copy them next to eachother (efg from table 2 copied into fgh in table 1) then scroll through to make sure e and f match up all the way down. If there are only a few places where they don't match you can insert cells as needed to make them line up. Example:

Column E reads: A, B, C, D, E
Column F reads: A, B, D, E, F
You had a value in table 1 that didn't have a matching value in table 2. You can highlight the cell in column F that contains "D" along with the two cells to the right of it and insert cells, shifting cells down. Then you will have:
Column E: A, B, C, D, E
Column F: A, B, (blank), D, E, F

Once all values in columns E and F align you can delete one of the columns.

Or if you have too much data to make this method practical you could write a macro that does it all.

Can't you do a vlookup on E to pull in F and G or am I not understanding?


So start by putting vlookup formulas in F and G on the ABCDE sheet.



You can copy and paste values to get rid of the formulas. Sort by F and G and clear out the N/As.

Now go back to the EFG sheet and put in Match formulas. Anything with an N/A is NOT on the ABCDE sheet. So sort by that column, then copy and paste those rows to the bottom of the other sheet.

^^^ That should work too. If there's a lot of data that doesn't match up that will be quicker. If it matches up 1:1 across each sheet though that's more steps than necessary.

thx! I solved it.

Got a question regarding Excel and Access.

I want to import data using Excel to Access, I do so using ADODB.
I go through a sheet in excel using a do while.
When there is an error, I want to write something to a textfile and resume with the next line.

How do I do this? I tried using On Error GoTo and On Error Resume Next and somehow it didnt react to it.

In general i don't like to use on error goto unless it is ending the function, coupling it with a resume next usually just gets messy.

What is causing the error? there may be a cleaner way to catch it then using on error

I am importing data and some data is already existing in the database. I use a unique identifier, I dont want to use an increasing ID.

So, my code (I dont have it here, it's at the office) is somethig like this:

Do while (going through rows)
add item
loop

Now, when I get a duplicate error, I want the macro to write it to a textfile and go on with the next row.

i would probably add something like

So, you would open another recordset and search? Isnt that too resource intensive? I sometimes go through worksheets with 5000+ lines.

I get what you are saying and it really just depends how big the table you are inserting into/searching is. If it's like a million rows then the extra search may add some time, if not then it shouldn't be a huge change. I'd try it and see what happens.

Is the whole db stored in access or is it using MSSQL back end?

Access only.

As I said, the max is likely around 5000 to 10000 rows.

I would try the first method, if it significantly slows it down then you could export the whole table to excel and do the duplicate search in excel then it would only be one extra connection

Is there a way to create a unique item only column based on another column?

Say column A has 6 unique entries, but they are scattered and repeated multiple times.

Is there a built-in function to do the advanced filter/copy/unique or would custom code be required?

Assuming data is in column A2:A100 and you want the results in B2:Bx

This is an array formula so when you enter it you must press CTRL+SHIFT+ENTER not just enter (you will know it works if it shows with { } on either side of the formula) then you can just copy and paste it down column B until you start getting "N/A"

I tried it and it doesnt slow it down significantly.

Thanks for your help

Appologies if it's been raised already but is there an easy way to stop cells defaulting to wrap format when you paste into them?