Dynamic Variables

[kreitzig]

I'm looking for code that will allow me to load varibles dynamically.

For instance, if my sheet were the following table:

Fruit	Orange	Naval	Orange
Fruit	Banana	Big	Yellow
Fruit	Orange	Valencia	Orange
Veg	Lettuce	Romaine	Green
Veg	Avocado	Haas	Green

And I wanted to be able to count how many unique items I had in the first column (which would be 2) and then I wanted to write that to info to another column along with the data, how could I do that? This is also assuming that the first column will be different every time I run the macro. If the tables aren't enough I could attach a sheet that show a little more detail in what I'm looking for.

Thanks.

[oeldere]

Maybe with an pivot table?

[kreitzig]

Thanks for the response.. I'm not sure if that will work. I have code that takes data from a spreadsheet and puts it into Word. In this case, I am wanting to be able to create a list from data that is in a single cell, take that and run it against my sheet, and also count the characteristics. I tried writing this out with the fruit analogy, but it was getting crazy, so let me change it.

I want to creat a list of radio frequencies from a single cell, "97.9, 99.1, 101.1, 102.3")
For each frequency in the list i want to record data that is offset in another column, counting how many of each there are.

Please see the attached file, maybe it will help make this clearer.

Book1.xlsx

[Palmetto]

I haven't look at your workbook (was working on this when you posted), but see if this will work for you.

Please Login or Register  to view this content.

Change the sheet name as needed. The code assumes row-1 is a header (required), counts columns and loops through each using advanced filter to extract unique values and the place a count in the adjacent column.

[oeldere]

I made 2 pivot tables (same data / differant lay out).

[kreitzig]

I appreciate the responses. I'm not real clear on how to extract the information from the pivot tables. The data is being taken from Excel and used to populate a form in Word. As for the loops, I'm going to play with this a bit and see how it works. I will post back tomorrow.

Thanks again, and Happy Holidays.

[kreitzig]

One real quick question before I go...

Is it possible to pass data from a single cell, in this case separated by a comma, into an array instead of using a range of cells?

[oeldere]

Not quit sure, what your up to.

Text to column?

Excel 2007 => data => text to column

[Trebor76]

Is it possible to pass data from a single cell, in this case separated by a comma, into an array instead of using a range of cells?

Yes, try this:

Please Login or Register  to view this content.

Merry Christmas!!

Robert

[kreitzig]

Perfect.

Merry Christmas to you, sir!

[Palmetto]

Is it possible to pass data from a single cell, in this case separated by a comma, into an array instead of using a range of cells?

This is quite different from the description of your need in your first post. If this was your original need it should have been clearly stated from the start or a new thread started since it is a different question than extracting and counting unique values. I am very happy to help, but now think I've wasted time on a solution you didn't need.

[kreitzig]

This is quite different from the description of your need in your first post. If this was your original need it should have been clearly stated from the start or a new thread started since it is a different question than extracting and counting unique values. I am very happy to help, but now think I've wasted time on a solution you didn't need.

I apologize for the confusion. Obviously I was still trying to articulate it properly.