Need an Excel Guru
 

I have a parsing (sp) challenge with an Excel spreadsheet. Any gurus out there?

No guru, but what is the issue ?

essentially I have a spread sheet (many) that has data arranged a certain way. I need various cells to be in a very different format. The best way I can explain it is to show you. Can I shoot you two spread sheets? One of the data I have and the other of the data table that needs to be populated?

Say the data I have is defined in column A and the values are in B, C, D, E.... Bit I need the data to be in Rows. So lets say column B row 5 data has to be in Row 8 Column C. The data in the rows has to parse to the date of the other spread sheet columns.

I know it can be down. I remember in grad school with a very old version I did macros to do something like this. It must be simpler now. I just never mess with it.

pm sent

didn't get it

Please post the solution! I do all my data manipulations for excel in R, but always interested in how people solve data problems

Boy, if I can't figure out an email, then it's hopeless to help with your spreadsheet.

Pm, tried again.

I sent you one of my email.

Vlookup, lookup formula or sumif formula can be pretty powerful tools. Not sure if these will work for your situation.

ok, may be this will help. I want cell B5 in spreadsheet 1 to fill in C8 in spread sheet 2.

I love Excel.

That said, for those cases I usually do some kind of labelling. I create an intermediate table that contains the rearrangement instructions (like a label for each data position) and then I create formulas in the final table that populate it with the data from table 1 using the instructions from table 2. This way it is easier to troubleshoot.

If you can share your problem I'd love to give it a go.

You should be able to use a nested conditional formula to populate the necessary fields. For example, you can have it first look for the correct date and then go to the data field for info. You can even have the nested formula look thru an entire spreadsheet, find all the matching data and have it summed in the appropriate field.

I'm no expert, but the pivot table function in excel could be the answer.

If you haven't found a solution yet, I'm happy to help...I work with crazy complex excel sheets all day long.

Same here Mike. Send me the file and what you want and I can take a look at it.
Send it to MBAtarga at gmail dot com.

Is this what you are trying to do?
 

I'm no guru, but what you described can be done as follows. My version is Excel Professional 2010. Open your Spreadsheet1. Open your Spreadsheet2. To make things easily visible, click the 'View' button, then 'Arrange All', then 'Vertical' so that you can see your two open spreadsheets side-by-side. Now, in Spreadsheet2, go to cell C8. Type the equals sign "=", then click on cell B5 in Spreadsheet1. Hit enter, and the contents of B5 in Spreadsheet1 will populate into cell C8 of Spreadsheet2.

The formula in C8 of Spreadsheet2 will look like this: =+[Spreadsheet2]Sheet1!$B$5

Is this what you are trying to do?

Mike - I just sent you an example for a solution. It was sent back from my home email - different that the above.

Mark

Please post a screen shot of the data now and what it needs to be. Also state the criteria for what you are going to do. Please also indicate the Excel version you are using.

There is a function where you can take column data and convert to rows.

Happy for you to e-mail me. We can talk on the phone and I can more than likely pull it off for you.

If it's an onerous set of data, I can get a handle on what you need. Perform some due diligence to ensure it can be done and teach you.

If it's a couple hour job, I can do it.

23 years as an operational accountant.

Paul T sounds viable as well.

Will PM you my e-mail.

You can do this on this thread and get several options to do the same thing. What I would want to know is what is the most efficient solution. No reason to PM.

Ok. Once the speed bump is articulated, fine.

Here is what you have to go on.

This means the data in the rows has to be parsed to allow linkage to the sister spreadsheets. You know that. Data still has to be linked, the hard way.

We don't have (understand) the basis for doing that yet.

My solution involved use of vlookup. I sent the revised file back to him for him to look at - it was only a partial solution - I didn't complete the spreadsheet because the data was not consistent in terms of labels between the files. He basically needs a table of raw data - where each new set of measurements that is added as another column on the right, converted to be rows of data under columns on another spreadsheet.

If you can work vlookup today, you are seven years ahead of me. Function is the same. I am just rusty.

How inconsistent is the data?

Mark,
Sorry I didn't respond sooner. This is exactly what I'm looking for! I can provide a spread sheet map if you will where cells in one equal cells in another if that helps? I will look to get on it in the PM tomorrow.

Lubey, why not post the screen shots?

Be careful with hlookup and vlookup guys. Data needs to be sorted in ascending order which sometimes is inconvenient. Index-match is the method I use these days - see Say Goodbye to VLOOKUP, and Hello to INDEX-MATCH | eImagine Technology Group for a good explanation.

You also can't use multiple criteria with h/vlookups

Array formulas are voodoo