Formatting numbers and searching across worksheets

Hello,

I was wondering whether anyone might have some advice. My Excel skills are OK, but I've run into something that's beyond my knowledge.

I am running a report at work (I work in a university library) that examines the relationship between print and electronic versions of the same titles, e.g. what does a student read if given the option of both?

I have pulled out data sources from a number of places and unfortunately the spreadsheet in 22MB so I can't share it here - if someone has a creative idea for how to host it, that would be great.

The first issue is the number formatting - Excel has automatically turned these 16/17/18 digit numbers into +14/15/16. If I format the cells to text, it does not revert back to the full number. Also Excel has imported the numbers and lost some of the information.

e.g. on my second sheet (All Loans 2017-18) Column H in Line 2 is 9924317910302020 - this should be 99106404710302021. Basically the last two numbers of each number (and there are several thousand!) should be 21 instead of what they are. Is there an easy way to 1) format the numbers in one fell swoop and 2) to change the last two digits of each number to 21?

All sheets have ISBN/ISN which should be unique identifiers.

As the sheet is currently filtered, I need to search across every line in RL Items 17-18 and find the following values in the next four sheets:

Sheet 2 - Column A (Loans)
Sheet 3 - Column AE (No of Views)
Sheet 4 - Column I (Reporting Total Period)
Sheet 5 - Column I (Reporting Total Period)

There are over 3000 lines in the RL Items 17-18 sheet as it is currently filtered so if there's a way I can do something without manually looking through every line, over four other sheets, that would be great!

Thanks very much,
Kevin

Thanks avk. I have uploaded a small sample of the first sheet - it's the column highlighted in yellow.

The image shows what these numbers should be. This is taken from the software used to generate the report.

So what I need to do is take the formatting off to reveal the full number (format cells + text doesn't quite do this - it still retains the +15 and I'd have to click into every cell in that column to get the full number) and then replace the last two digits of each with '21'. Is there a way of doing this?

I've also added small samples of the other worksheets - the plan would be to merge these into one document.

All should have ISBNs in common. Is there a quick way to find the data I need from each set of ISBNs? I would be working through each of the ISBNs in the RL list - there are around 3000 here.

e.g. UG PG Loans 17-18 I would need the loans amount from Column A
e.g. DCS I would need the number of views from Column AD
e.g. Dawsonera I would need the reporting period total from Column F
e.g. Ebook Central I would need the report period total from Column I

Maybe what I'm doing isn't possible without manually searching through each worksheet one at a time?

Many thanks,
Kevin

Thanks!

I think I need to try a VLOOKUP function but I don't think I've quite got the hang of it. Here is a sample of the massive spreadsheet. It's the MMSID that both sheets have in common. What I want to do is use VLOOKUP to find the loans/return figures (from Sheet 1) for the MMSIDs in Sheet 2.

In the full list, Sheet 1 will have many more MMSID entries than in Sheet 2, so the aim with the MMSID entries in Sheet 2 is to get those loan/return figures quickly.

Thanks!

Perfect! That's worked - thanks very much. I was trying the VLOOKUP and messed my formula up that it kept returning the MMSID/LCN from the first sheet instead of the loans/returns. But this makes perfect sense! So when I try this again I should know how to do it!

Thanks!