combining duplicate rows with multiple columns

[jharrison209]

I am getting an excel file from a client that consists of multiple 'descriptor' columns (sample date, received date, project number, sample number, lab name, etc), and then multiple columns for each sample value (i.e. pH in column M, EC in column N, etc.). The problem is that the client will put one value per row - so in row 162 they will have all the descriptor information and a value in column M. Then in row 163 they have the same descriptor information and have a value in column N. The whole document is currently about 40 000 rows long (...), and rather than deal with the data in this way we need a way to consolidate the data so that each sample has all its values in the same row. The problem is the multiple descriptor rows - I haven't been able to find a way to consolidate the data that allows me to choose multiple columns to say the same - help?

[noboffinme]

Hi jharrison209,

Without seeing the file, a Pivot table may help you.

Can you post the file or a dummy data example to look at?

Thanks

[jharrison209]

Sorry for the late response. I tried using a pivot table, but the problem is some of the values are below threshold (i.e written as <0.02) and the pivot tables keep returning them as 0 (which I don't want). Is there a way to get it to return the proper value? That would fix my problem...

If not I'll put together a dummy file to upload here.

[noboffinme]

Try highlighting the Column with the Values (Calculations) & Right click to select 'Value Field settings' >> 'Number Format' & change the Category to 'Number' & select the number of decimals you want the Pivot to present.

Hope this helps.

[jharrison209]

Sorry, I explained myself poorly. The starting data returned a result that was below threshold - i.e., the value in the cell is literally '<0.02'. The problem isn't the significant figures, it's that the pivot table is interpreting that cell as text since it contains the symbol '<', and is thus returning 0 because the max of all the other (non-text) cells is 0.

I ended up doing a workaround by changing '<' to '9999999999' in the original file, sorting the pivot table the way I wanted it, and then changing it back after I copied&pasted the table back into a worksheet. But I really don't like that solution, is there anything else I can do?

[jharrison209]

I updated my question with a dummy file here -> http://www.excelforum.com/excel-gene...ml#post3705270

[noboffinme]

Have a look at the attached file.

I've created a summarized Pivot & also an Unsummarised Pivot.

Let me know if this is closer to what you need.

Re the figures written as "<0.02", can you use a negative number instead?

[jharrison209]

Thanks for taking a look at it! Unfortunately, it's not quite what I need. In cases where there is a duplicate data point (For example in Denny's Saltiness), I need the second data point to go on a different row. I can't take the sum OR average them in any way, nor can I simply take one and ignore the other. This is where I'm running into the most problems.

The point about negative numbers. Yep, that's a better idea. Thank you for that.

[noboffinme]

OK, try this one.

I think this is about as close as a Pivot will be able to do this.

It's pretty close to what you've posted.

Cheers