Collating data in several columns

Since the data breaks down into identifiable pairs of data, you can do a VLOOKUP on each subset. Here is a sample using a VLOOKUP for each Month's two column pair.

Just adjust the ranges to your actual data and drag down, it will reorder the data into the desired format. You just need to put in the Month headers and CC01>>CC08 row labels.

Post up a real sample so we can see what we're up against.

Ok, have attached an example similar to the type of thing I need to work with.

For me the difficulty is all about getting the list of team names that appear in all quarters so I can use a Vlookup on them to get the information to compare teams by quarter and put a zero in if there was no value returned. The latter part I can do, its just finding the easiest (quickest) way of getting the names.

Hope you can help, and hope the example makes sense.

Oh and I have Excel 2007 at home, but unfortunately on Excel 2003 in work, so if the solution is practical to work with 2003 I would be grateful.

Thanks.

PS. I know that once I have copypasted all team names from all quarters being referenced into one column the pivot table function will summarise them into a nice list, would prefer to just be able to select the table and get this information.

There's a Excel Add-in called MOREFUNC, I've embedded into this sheet, gives you a bunch of other functions not normally in Excel. The one we're using is called UNIQUEVALUES, and buried inside an INDEX functions, will give you the list you want. But your resulting list is LONG, it's a lot of calculation, so I turned off AutoCalc in the sheet. Press F9 anytime you want this sheet to recalculate, or just turn it back on after you've copied your data onto the DATA sheet.

The CHART sheet compiles your data after you copy onto the DATA sheet. The column that does the TEAM names uses a named range called "Teams" and will only return text values, it skips the numeric answers, just drag it down the A column until it stops giving answers. The formulas for row 2 on DATA all pull answers from a different subset of the DATA, so you need to set up the first row then copy that down. I've already done that, it's just important to realize if you expand the data set out to more columns in the future.

Look it over. It's slow, but it works, and you can speed up things while your setting your data in by keeping the Tools > Options > Calculation > Manual setting active, then turn it back to Automatic.

Nice work, JB.

You could cut the calculation time in half by changing

=IF(ISERROR(VLOOKUP($A2, Data!$A$2:$B$120, 2,FALSE)), 0, VLOOKUP($A2, Data!$A$2:$B$120, 2, FALSE))

to

=IFERROR(VLOOKUP($A2, Data!$A$2:$B$120, 2, FALSE)), 0)

... using either the native Excel 2007 IFERROR function, or this equivalent:

Please Login or Register  to view this content.

Also, for your UniqueTeams function, time for you to take a look at the Scripting.Dictionary object.

I really hated the delay, and coerced some simpler code to do the work.

If the data is this wide or smaller, just pressing the GetTeams button on this version and it will quickly build your chart.

Thanks, SHG, I incorporated your UDF into this version as well so, IFERROR formulas are in effect. Reading? Oh my.... that would make things easier, right? Not sure I'm ready to start studying again, hehe.

Hope this solves it for you, Cardiff. If you want to adjust the stuff it's doing in the GetTeams macro, it should be pretty straightforward. You can do it.

Understandable. I realized at church tonight that you might try running the macro twice over itself without resetting the chart, so I added another macro to make getting the chart ready to "run" again easy, this version has a "Reset Chart" button that clears the data that's there already.

There's also an "AddFirstRowFormulas" macro you can use if you ever accidentally clear the base row(2) of formulas, since those are the ones needed to copy down.

I'm off to bed now, myself. Cheers.

This is totally Excel 2003 compatible, that's what I use.