Return results from duplicate values in multiple columns... Please help!!

**spamela020** · 06-13-2015, 03:07 PM

Hi all,

I'm basically a complete noob to excel (other than the basic SUM functions) but have been tasked with creating a system to track plant and tool hire for my company. So far I have managed to learn a few new tricks on excel (such as V and H lookup, IF OR and AND) and these have enabled me to create a sheet which tells me when an item is available, when it's currently on hire and the number of days it has been on hire for. I am now trying to create a 2nd sheet which will look up the job which I have booked the plant to, tell me what item they hired, how long for and how much to invoice. The problems I'm facing are: the job number may have been entered multiple times in the 'job no' row, and there are 10 'job No' rows on the sheet. I wondered if there was a formula or combination or formulas (I don't mind hiding columns) to ask excel to look in all 10 of the 'job no' columns, find every time a certain job number is used, then return the data from 3 cells in that row. I have scoured the Internet and cannot find a solution that makes any sense to me! Please bear in mind that I have no clue what a macro and a VBA are! (Though I will try them if needed, but will need a tutorial on how to do it!) I would really appreciate any tips / advice / suggestions.

Thanks for reading!

Sam

**Richard Buttrey** · 06-13-2015, 03:14 PM

Hi and welcome to the forum.

It's more than likely that a Pivot Table will give you all the information you want. It will automatically allow you to summarise data for your jobs without needing to resort to formulae of any sort.

Upload the workbook if you want more specific help.

**popipipo** · 06-13-2015, 03:16 PM

Can you upload your excel file (without confidential information) that wil help.

**spamela020** · 06-13-2015, 03:39 PM

Sorry, Give me 10 mins, I have been working on it on my ipad and I can't seem to upload it from there. Will transfer back to my laptop and try again.

**spamela020** · 06-13-2015, 03:52 PM

I have tried to upload but maybe it is too big (as there is a lot of data on it), and it's not working. Ask me any questions and I will answer as best I can... Or I can email it? Any advice on how to use a pivot table? Thanks again.

**newdoverman** · 06-13-2015, 03:59 PM

If the file is greater that 1MB Zip it and upload. Without a file, even a guess of what you really want would be too difficult to give.

**Richard Buttrey** · 06-13-2015, 04:05 PM

We only need to see a representative sample of your data.

Take a copy and delete all but say a few hundred rows and upload the reduced file clearly explaining what results you expect to see.

**spamela020** · 06-13-2015, 04:48 PM

Ok, I think I have finally managed to do it! Apologies for the delay, it took ages to delete the data!

**spamela020** · 06-13-2015, 05:00 PM

So, what I'm looking to achieve is a table which will analyse how many items have been hired to each job number across the whole month (could be up to 10 columns), then tell me what items those are and how much i need to invoice them.On the 2nd tab of the sheet I have just uploaded, I have experimented with a pivot table, but I can only seem to make it work for one of the sections at a time, I would need to see all sections in one. Any help would be great! Thanks.