create a list from data in a spreadsheet

[RobSA]

Hi folks,

I have a spread sheet that entries numbered 1-500, in column A, I have numbers in another column that relate to list the days outstanding.

I have used a formula to count the number of times the days are outstanding between 1 & 10, between 11 & 20 and between 21 & 30, continuing until I exhaust the range.

What I would like to to is for each time it identifies an occurrence, that the listed item in column A is also recorded in an open cell.

I would appreciate any advice.

 

[Ramesh]

Hi,

It would be helpful, if you can provide a sample spreadsheet.

Thanks,
Ramesh Kumar.P

 

[RobSA]

Thanks for the prompt reply. Please see my work in Sheet 1

 

[Ramesh]

Hi,

I could see that you have calculated age in column AC of the Log sheet and then in column C you are categorizing the values in Sheet 1 based on age groups.

Now according to the above setup, can u plz elaborate your question plz, as it is little difficult to co-relate the above said.

Thanks,
Ramesh Kumar.P

 

[RobSA]

Hi Ramesh,

If I can explain differently.

The numbers listed in the column C8 to C32 in Sheet 1 are the occurrence between certain values.

What I am trying to do is automate the process of writing the numbers seen in column H of Sheet 1.

So if there is an occurrence of a number between say 201 and 210 then the value found in column B of the worksheet named LOG will be shown, i.e. 007.

If there are more values then additional values will be shown.

 

[Ramesh]

Please find the attached updated worksheet, where I have written the code to concatenate the values based on condition.

In Module 1, I have written the function ConcatenateIf to conditionally concatenate the values.

Using this function, i have populated the values in Sheet1.

Hope this is helpful!!

 

[RobSA]

Thanks, the idea is great.

Can I change the range from AC7:AC148 to AC3:AC502 without affecting th e overall result.

The range you have selected works, however with regular inputs I need to be sure taht the full range of information will be considered, under the various filtering conditions you will see in the file provided.

Thanks for your help so far.

 

[Ramesh]

Yes.

Here are the inputs to be supplied to the function:

1st parameter : The column that contains the values where you want to apply the condition. You can select the entire column if you would like to.

2nd Parameter : Lower limit of the condition

3rd Parameter : Higher limit of the condition

4th parameter : The column that contains the values which needs to be concatenated

5th Parameter : The seperator text, using which you would like to concatenate the values.

So, the input for 1st & 4th Parameters can be the entire column too

 

[RobSA]

Thank you

 

[RobSA]

Hi Ramesh,

Please could you assist with the formula for the part added at the bottom of worksheet 1.

I am trying to do the same as for what we worked on yesterday but just using data from different columns.

I think the concatenate option does not seem to work in this instance.

 

[RobSA]

Hi Folks,

In the attached worksheet I have been assisted in getting the data in the upper table (worksheet ) to perform correctly.

I my own effort to get a similar result in the lower table on worksheet 1, but this time following the criteria "IN REVIEW" (column T of the work sheet called LOG ) I am not being as successful.

I would appreciate your support in this regards