Continuous/Automatic Data Integration From Two Tables into One
Hello good morning sir/ma'am
Actually, I was having a problem with excel...
and was wondering if it was even possible (i.e - merging the data of two tables into a new table in such a way that if any new data was provided it will be Continuously/automatically incorporated)
if there is any video related to it can you please provide its link
once again very sorry for the trouble
and thanks a lot in advance
how do you want the two tables to be integrated?
Further post #2, please add an example of the integrated table.
And how (manual input / extraction from another source) are the "Purchase", "Sales" and "Bank Transactions" sheets updated? and with what frequency ?
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
first of all thanks for giving me your time...
as for the questions-feel free to ask if there is anything else
- I want to integrate Purchase & Bank Transactions(Payment part/dr part) / Sales & Bank Transactions(receipt part/cr part)... if it will be a problem feel free to separate them into receipt and payment tables...
- Sales, Purchase, Bank Transactions(the first three sheets) will be entered manually and then will be combined in groups of sales+reciept(bank transactions) & purchase+payments(bank transactions)
- the Sales, Purchase, Bank Transactions sheets will be updated everyday...
- I have improved the attachment file
Last edited by Gaminator; 05-03-2022 at 01:47 AM.
@Gaminator, this is totally possible to do in VBA but might be more straightforward and expandable with Power Query (PQ), which is out of my wheelhouse.
@Carlmon thankyou for your time
@JohnTopley Sir any suggestions
See Sheet1
Please Login or Register to view this content.
Please confirm calculations in columns H and Q are correct then I will add to VBA
@JohnTopley Sir very sorry for the late reply,
And once again thank you for your help...
the calculations in columns H and Q are correct...
Last edited by Gaminator; 05-07-2022 at 04:37 AM.
Alternative solution using Power Query
andPlease Login or Register to view this content.
Please Login or Register to view this content.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
Hello Sir Alamsidman...
I am not very good with power query can you tell me how to apply it
@Alansidman Sorry sir I don't mean to be rude but,
But I think that @JhonTopley sir's solution better suits my conditions
Sir thanks a lot for your time
Updated VBA
Please Login or Register to view this content.
@jhonTopley Sir...
Do we have to run the program after every update?
Or, does it update at the time of opening of the workbook?
and I would like to make one more request,
can you please teach me how to customize the code so that it can be used in my original workbook
As requested:
Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").
It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.
- Follow this link to learn how to install Power Query in Excel 2010 / 2013.
- Follow this link for an introduction to Power Query functionality.
- Follow this link for a video which demonstrates how to use Power Query code provided.
@alansidman Sir thanks a lot for the help...
I was able to apply the code but it was showing an error
Expression.Error: The import Table24 matches no exports. Did you miss a module reference?
sir what should i do
Yeah,
That is because you needed to load each of the tables into PQ Editor before merging them. Here attached is your file with my solutions
Thanks a lot sir, it works under normal circumstances but...
it causes problems in entries of partial payment
samplexxx.PNG
As in the photo of the above post,
the amount keeps repeating and is a problem in the computation of balance
and will also cause confusion for the reader
is there any solution sir...
Attached runs on Workbook_Open But I have left the "Run" button (delete if not required)
... and would it have been nice if your example showed such characteristics instead of wasting our time with a non-realistic example of your data.it causes problems in entries of partial payment
Sorry Sir, I didn't mean to waste your time in any way...
Sir, there was an entry of the partial payment in the first attachment that I posted(under the name CA & DC)
And I once again apologize for the unrealistic example of data,
sir I genuinely didn't know that the example will cause problems...
@john Topley Sir thanks a lot for your time...
Sir in the original workbook that I am working with there are many more worksheets as well as the purchase and sales tables have a lot more columns
So sir can i bother you one last time to help me alter that worksheet... i can mail it to you
Post the more realistic data sample here.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
As per Ali's reply: post a realistic sample workbook with ALL sheets included. The volume (number of rows) is not a problem BUT let us know the likely maximum for any sheet.
I am extremely very sorry for not replying sir...
Actually Sir I got into an accident and wasn't able to use my computer because of my hand injury
Sir I'll post the sample file in 5 min...
Very Sorry for the trouble
Capture.PNG
Sir Can you please remove the balance column from the VBA program
as it is having problems I can do it with Formulas(sorry I made a mistake in the picture the 1st one should be -310000 and 2nd should be -440304)
Sir here is the sample
Sir Please pick the Sales/Purchase Data from the second table in the sales worksheet
Sie, Very Sorry for the trouble and thanks a lot for your help
Last edited by Gaminator; 06-06-2022 at 08:45 AM.
@JhonTopley
Please Help
Help Please
Thanks for all the help So far...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks