I have a formula of =IF(COUNTIF(Dynamic!$1:$120,Fixed!$O$1)>0,IF(HLOOKUP(Fixed!$O$1,Dynamic!$1:$120,ROW(),0)>0,Fixed!$O$2,0),0) and I need it to also look at 2 more cells (Fixed!$P$1 and Fixed!$Q$1) to see if there is a number 1 in either cell to return. I've managed to change the formula to look at all 3 cells and only return if all 3 meets the condition but i need it, so if either cell contains the number 1. I have attached a test doc with the formula and what I need it to do and I'm looking for a formula as i have many of these to do where the subscription name is different but the friendly name is the same so should be combined into the one column, unless a macro can be made to combine all subscriptions with the same friendly name from sheet 'Fixed'. Thank you all.
Posts by BlakeReuben
Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.


bosco_yip WOW you genius! thank you soooo much this worked as expected, thank you again!

Yep hence I need to convert it to Minutes so I can calculate with it, but thank you for your assistance.

Apologies royUK I forgot to say only the Data Column (A) is what i get so i need a way of converting column A into only Minutes, Columns B and C was me trying to figure it out apologies.

Hello all, I am in need of converting data into Minutes. The data format is fixed at ## Hours ## Min or just ## Min and I need it all in Minutes so I can calculate the time with other data. Thank you.

cyrilbrd That's perfect Thank you & apologies on the delayed response.

Hello All, I am in need of assistance. I have a small table and I need a formula in cell A2 that gives "1" if all cells B3:B8 contain a 1, however I also need it to break down the results if any cell contains a 0. for example if any cell between B3 and B8 contains 0 i need the result in cell A2 to read out A3&B3, A4&B4 etc to ONLY the cells that have a 1. I hope this clear and there are 2 examples in the attached. Thank you.

Ok I will double check to see where i am going wrong. Thank you.

so the test file works as it should. I have not changed the macro only added more data to sheet 1 (Input)

Unfortunately it stops at oArray(j + k, 4) = iArray(i, 4 + k) I think it is my fault as this is for 1841 rows and courses for this macro in particular.

Carim was there a particular course you did to learn how to create these macros?

Yes the English should be ignored but if it is easier i can delete column C from the input tab before clicking on the macro.

Need to add a 4th column to the output on the VBA to calculate.

This is very strange as the formulas seem to be stuck. Input tab is data i paste in from another system and the layout is always the same, sheet 1 puts an "X" in the number columns if they are in the input file and then the macro works off of sheet 1. I know this is not languages but it is the same format. Thank you.

That works soo much better and much faster. I am having trouble changing the number of columns "Languages". I have changed the lstep to 29 and it is till giving me 6 results per code

In this example it is 29 columns and upto 5000 rows, however the rows data is by formula and it doesnt seem to work here.
*EDIT* in sheet 1 the code should be =sheet4 B2 as 9999 the person code but that makes it worse.

it could be upto 3000 rows.

Yes I do know how many languages but it is different for each sheet, hence i tried to edit and realized it doesn't play well with formulas.

Carim, to the rescue once again. Thank you so much, it works perfectly.
