Find every cell in a sheet that isn't zero and make a list incl. other cells in row

Hi guys, just trying to work out if something is possible or not.

Our customers send us templates that are not in a standard format. I need to convert the 2 dimension sheets into a flat file with just the cells that have a non zero value.

What is consistent is that each row has a name in the first column and it needs to bring back that name, the column header and the value for each cell that isn't zero.

I'm not asking for someone to code this for me. Just want to know if a task like this is achievable before diving in.

Thanks for your help.

Regards HT

With the meager info, try this (Change references as required.)

Please Login or Register  to view this content.

If your layout is as in Post #3, this should be faster if you have a large range.
Again, change references as required

Please Login or Register  to view this content.

Originally Posted by mike7952

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Hi Mike, and thanks for your help.

I have attached some sanitized sample data - sheet1 shows a report from a customer that I'm trying to get into the list format. Sheet2 shows an example of the output I would need. Any pointers would be greatly appreciated.

Thanks again.

This should do.

Please Login or Register  to view this content.

Originally Posted by jindon

This should do.

Please Login or Register  to view this content.

Thanks Jindon, that certainly worked

Just a quick follow up iof I may because I'm not just here to take other peoples work but also to try and understand the code. How would I go about adding an additional column of data to evaluation process?

So at the moment I get what the code is doing - looking at columns 2,4,8,9 so adding another colum to that list is obvious but i'm a bit lost with the loop section and the 'i','ii' & "iii" bit - I think I need to add something here but I'm confused how that section is working.

Thanks again. You guys are so helpful.

The code is a extract of col.B, D, H & I from the data range into an array "a"

Please Login or Register  to view this content.

So, 1st row of Array a looks like "EE's Ref", "Surname", "Annual Salary" and "Annual Car Allowance"
Code loop from 3rd column to 4th column and down from row2 to the end of the row evaluating if the element is not 0 and not empty.
When something in there, put the relevant values to array "b" (for the output).

If you want to add columns to be evaluated, just add coloumn reference to the array like

Please Login or Register  to view this content.

and change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

So that the code will loop from the 3rd column to the end.

HTH

Thats brilliant Jindon, Thanks so much.

I just needed to also change:

ReDim b(1 To UBound(a, 1) * 2, 1 To 4) to reflect the number of columns and it worked.

Really appreciate the time and effort in helping me guys.

I'll marked this as solved.

That's good to hear.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.