Combining multiple sheets

Hi

Im trying to get the list for each page MP's MEP's and Lords) to populate the combined list with one starting where the last one finishes.
Some of these lists have over 800 entires so I've just done a few rows in each one.

Regards

Mark

Try this tool https://www.rondebruin.nl/win/addins/rdbmerge.htm

Hello Mark,

If you're just combining all data from each source sheet to the destination sheet, then this should do the trick for you:-

Please Login or Register  to view this content.

I hope that this helps.

Cheerio,
vcoolio.

Hi vcoolio,

Thanks for your reply.

I like to think I'm quite good with excel, but not as advanced as you obviously

I used to teach low level IT to people trying to get back into work but it was only up to level 3 in the UK (which isn't that high, about GCSE level)

I've created a few 'raw' systems for people (over ten years ago) and only got as far as recording macros and using the commends in loops and nested statements.

I've watched a video on youtube of a guy who happens to be doing exactly what I want but hes created the solution using a filter function.

Ive gone over and over it and am 100% certain that I've followed it to a tee but I'm getting an error message. Mate Ive paused it every 5
seconds and can't see what I'm doing wrong.

I can't post the link due to the fact I havent posted enough times, but if you google 'combine data from multiple excel sheets' there are
3 youtube videos displayed and its the right hand one

Sorry its 17 mins long but you obviously dont have to watch it all the way through as each sheets data retrieval command is the same.

I feel that would be far easier for me to manage as I actually understand whats going on there.

I never got up to the level of being familiar with short VB program that you've so kindly posted, so would need some help in how/where
to apply it?

Any help you could offer would be amazing

Thanks

Hi Ali

Many thanks for your reply

I'm using office Professional Plus 2019.

Last time I done anything even slightly complex was over ten years ago

So any help you could offer would be greatly appreciated.

Reagrds

Mark

Hi Pepe

Many thanks for you reply

Upon checking the specs on the add on you've sent me it states only up to 2016 version I'm using 2019 Professional Plus

Do you know if there is one available for 2019 version?

Regards

Mark

Hi Ali

I have already posted the file but here it is again.

Thanks for very quick response

Regards

Mark

Hi Ali

Thanks for your reply, I had some difficulty in updating my profile, it seems fine now though

So, I dont have three CSV files to work with. One source (MP's) was a CSV file converted to xlsx. The MEP data I've only managed to get today but is a standard xlsx. I am still awaiting a freedom of information request for the lords details.

Regards

Mark

Hello Mark,

Just going back to your post #5, I couldn't find that particular video. However, you mention that he was using a filter to have the rows of data transferred to the Combined List worksheet.
Does this mean that you have a particular criteria in one of the columns to filter on for the purpose of selecting which rows of data need to be transferred over?

Cheerio,
vcoolio.

Hi Coolio

I noted that from the video the guy was using a column that could be used to be the criteria, in this case he chose the date,

My basic aim is to have a master list which includes the email of each of the entries to be used in a mail merge exercise. When each subpage is updated the master will obviously update. Probably would have been much easier in access but the guy I'm doing this favour for cant use access

The problem with the data is that its from CSV files, excel files and XML files, which is the one I dont know much about and is proving the problem as on the website for the Lords there is a CSV file but email addresses are not included. The data is there though if you click on each members profile indvidually. Is there a way to get them into one place ??

Hope this makes sense and thanks for your reply

regards

Mark

Hello Mark,

You can save all of those as .xlsx files on your machine. Once you've done that, select the information that you require and place it in a new Excel file. Use this new file as your main workbook. You'll then find that the code in post #4 will work or we can amend it to use a filter on a particular column.

The other option is to have AliG show you how Power Query can solve this for you as well. I've never taken to PQ so ALiG would have a better understanding of it.

Cheerio,
vcoolio.

Hi VCoolio

I did try the PQ myself but again tried to follow a tutorial which didnt got to plan. Can you answer something for me. One of the data sources I have has a viewable XML link. This then takes my to a HTML style page with data looking like HTML coding, however I cannot download the data nor can I use any Save As function, can you advise on that.

A friend of mine has actually done a FOI request for the whole dataset in the correct format but that takes 20 days for a response so I'm still plugging away.

One thing I defo need to do is some form of refresher course because I used to be able to create a solution for most things I encountered.

Regards

Mark

Hi VCoolio

All sorted mate with the PQ, cant beleive how easy it was to be honest

Anwyay, Thanks for all your advice

Regards

Mark

Good day Mark. No worries.

Good to know that you've worked it all out using PQ. Yes, it is pretty easy to use but for some reason I've never taken to it and generally avoid it. I might have an epiphany about it one of these days!

Good luck with your project.

Cheerio,
vcoolio.