Excel - How to create dynamic range based on column content 1

[amrog]

Can experts help?

I have a 2 columns; A containing product_id and B containing item_id. Each row contains the product_id and one of its item_id. Suppose rows are sorted so that all product_id, item_id are ordered by product_id.
I need to construct a row range for each product_id; that is for product_id (1) -> row ranges from item_id (1.1) up to item_id (1.n) and product_id (2) -> ranges from item_id (2.1) up to item_id (2.n), etc...

Let the row range formula be in a third column C that is filled according to the rules above.

 

[SkipVought]

Hi,

"I need to construct a row range for each product_id..."

You can, but that's not the best approch.

You can create a Named range for each column of data. I'd recommend starting each column with a Heading, like product_id and item_id

Your next requirement is very unclear. How about posting some sample data that illustrates you needs.

Select ALL data including the column headings.

Insert > Name > Create - Create Names in TOP row.

Now ou have TWO named ranges; product_id and item_id



Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[amrog]

Column A Column B Column C
1 Product_id Item_id
2 P1 I1_P1 A2:A4
3 P1 I2_P1
4 P1 I3_P1
5 P2 I1_P2 A5:A6
6 P2 I2_P2
7 P3 I1_P3 A7:A10
8 P3 I2_P3
9 P3 I3_P3
10 P3 I4_P3
..

This is what I have : Column A and B and I need to have column C.

 

[SkipVought]

Check out these functions...
[tt]
COUNTIF()
ROW()
IF()
[/tt]


Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[amrog]

What will be the formula to be used in Column C to provide the output indicated?

Please note that the content of Column A and B in the example are actually names with no explicit relation based on the description. Say Codes for example.

I am saying this in order not to consider Column B containing text of column A. This is not the case.

 

[SkipVought]

[tt]
2 P1 I1_P1 A2:A4
3 P1 I2_P1
4 P1 I3_P1
[/tt]
formula in row 2:
ROW() returns 2
COUNTIF() returns 3
ROW()+COUNTIF()-1 is last row in range.
Concatenate "A" with ROW() with ":A" with last row in range. Concatenate like "A" & ROW()
IF() test column A value with Column A PREVIOUS value. If they are not equal, then display the range else ""

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[SkipVought]

in c2
[tt]
=if(a2<>a1,"A" & row() & ":A" & row()+countif(Product_id,a2)-1,"")
[/tt]


Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[SkipVought]

What do you intend to do with these results?

Seems that you'd want to return one of the item_id values.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[amrog]

Thank you SkipVought.
Your solution is straight forward to my need. Although it looks simple, but I am messed with data analysis and was blocked and unable to think for the solution you provided.

The reason for what I need is to monitor the sales patterns of products through LINEST(). Column A actually contains the product code, Column B contains the sales date and Column C contains the actual sales. I need to get for each product the LINEST() parameters using the formula you provided. X-values will be Column B and Y values will be Column C. What I needed is the dynamic range LINEST will use for each product.

Again, thank you very much indeed for your fast support.

 

[SkipVought]

rather than a TEXT result, you need to have a RANGE result.

Check out the OFFSET function, which returns a RANGE for either the x or y.

Check out HELP. There are 5 arguments.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}