2 dimension / 3 category data into a 3 column list

[jgsugden]

'Sheet One' has a bunch of data. Column A is a list of Authors and Row 1 is a list of years. Each cell from B2 (over and down) indicates the title of an article that an author submitted in a particular year. For example, B1 shows the author James Johnson, A2 states 1905, A3 states 1906, cell B2 states the name of the article James Johnson wrote in 1905, and B3 states the article James Johnson wrote in 1906. All articles are unique. There are over a hundred columns and over a thousand rows. Many cells are blank (meaning there was no article for that author in that year).

What I want to generate is a three column list on Sheet 2. Each article will appear on the list. The second column will indicate who wrote each article. The third column will indicate the year it was written. I don't care about the order that the articles are listed, and can tolerate there being blank rows. Any ideas on how to generate the list?

The second trick here is I need to be able to swap in new data and generate a new list. In other words, I'd like to be able to past all new data on Sheet 1 and generate a brand new list for a different set of authors and articles.

Any ideas? This can't be a unique problem, but I can't find attempts to do something like this elsewhere...

Edit: I've attempted to attach a sample mockup of the problem. Sheet 1 has a sample of the data. Sheet 2 has an example of the results I want to generate.

[alansidman]

It would be very helpful if you supplied a sample worksheet, not the full worksheet so that we can write some code and test it.

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 scroll down to Manage Attachments to open the upload window.

View Pic

[jgsugden]

Added the worksheet.... No ideas?

[MrShorty]

I don't know how others would do it, but I would use the INDEX() function. Something like this:

Please Login or Register  to view this content.

That's how I'd probably do it.

[jgsugden]

Thanks.
That gives me three columns with the correct data in them, but a lot of extra unnecessary lines. I have an inelegant, but workable, solution to get around that, though.

[newdoverman]

Something like this could work if you expand the formula to accommodate more than 2 books. This would result in a long formula with many IFERROR statements to accommodate the extra columns.
To collapse the horizontal arrangement to get rid of blanks enter this where you would want the condensed matrix of author and books
Enter with Ctrl + Shift + Enter
I entered this in A15 and filled down and across.

Please Login or Register  to view this content.

To get the years for the above (references would have to be changed to match the array location

Please Login or Register  to view this content.

There are other formulae in the workbook enclosed.

[newdoverman]

Here is my first reply filled out to accommodate the data range that you have laid out. This makes use of tables and then filtering out the blank rows. There are strange results without the filtering but the filter takes care of that.

[MrShorty]

Thanks.
That gives me three columns with the correct data in them, but a lot of extra unnecessary lines. I have an inelegant, but workable, solution to get around that, though.

I'm sure a filter operation would get rid of the empty lines.

[WHER]

If you are willing/allowed to use VBA, check out this approach.
Click button on sheet "Results"

Please Login or Register  to view this content.