Transpose and create duplicate names

Hi

see how this goes.

Please Login or Register  to view this content.

rylo

Thanks rylo but it came out like this with the parameters I gave:

dog 12
dog 13
dog 14
dog 15
dog 16
dog 45
dog 76
dog
dog
cat 78
cat 22
cat 89
cat 78
cat 63
cat
cat
cat
cat
mouse 32
mouse
mouse
mouse
mouse
mouse
mouse
mouse
mouse


It's not picking up the 3rd column and I don't want the name there if there is no data for it. Like in my example,there should only be one "mouse",five "cats" and seven "dogs". Also the offset part will not work because the sheet is actually 1500 columns wide and I was just giving an example.I need the macro to use the range.

Hi

To avoid confusion on interpretation, put up an example file, and show how the example data should output.

rylo

Hi

Here goes.

Please Login or Register  to view this content.

rylo

Thanks again rylo. That did work correctly. In actuality the data I want to retrieve is in columns 826...835 and in 846...855 with other data all around these ranges. What adjustments should I make to the code?

Hi

Change

Please Login or Register  to view this content.

to be

Please Login or Register  to view this content.

rylo

Thanks again but am now getting a run time error 1004 after executing this line

OutSh.Cells(outrow, 1).Resize(dataitems, 1).Value = ce

Also in your changes you show two

(outrow, 3)
(outrow, 3)

I assume the first should be (outrow, 2). Any rate tried both ways.

rylo

I think I figured out why I get that error. Its because in the real sheet there are no empty cells to separate the data ranges. I inserted a space between the ranges and the code worked but the results are a little sketchy in that a lot of names only got partial info.

Here is what the real file looks like.

can't make any sense out of that file. Can you pick a couple of column A items, show the result for them and explain exactly where the data comes from in sheet1.

rylo

Leith, thanks for the effort but only the name column came out right. The rest was strange.

rylo and Leith here is a sample of what the output should look like. As you know, the names are in the first column. Then as previously mentioned I am trying to get the data in columns 826:835 and in 846:855 transposed.

Can I try too?

Please Login or Register  to view this content.

nilem

Hey, thanks a lot. That worked great. Only the output should be on sheet2. A question on your code. If I want to expand on it and create more columns of result from different ranges,(currently we have 3 columns of results) what part of your code should I change?

For example, add one column in the output array.
The code will change that:

Please Login or Register  to view this content.

The array output to Sheet2 (which is placed a green arrow).
If you want to add a lot of columns, you may need change the whole code.

Thanks nilem. That worked too. Up to how many additional columns do you think would be too many for the code.

You will see it for yourself, when code will run too long (more than 30 seconds, for example) or you'll get the message "Out of memory".

Thanks again nilem.