Hi,

I want the first 'match' column on each table & page (P/W/AE), to look up scores from the column to the left (O/V/AD), and see if it corresponds to the correct outcome/level (N/U/AC), against the criteria in the table (AK/AL). I also want the second 'match' column (S, AA, AI) to look up scores from three columns the column to the left (Q-R, X-Z, AF-AH), against the outcome/level against the criteria in the table AK/AL.

If ANY (1 or more) of these scores correspond correctly to the criteria given in the tables I would like it to show Y in column H. If none of these do then it would show N. If Y is already in the first match column, it would show Y in the second match column irrespective if those in the second set of scores do not correspond.

Thank you for taking your time to reply

I want the first 'match' column on each table & page (P/W/AE), to look up scores from the column to the left (O/V/AD), and see if it corresponds to the correct outcome/level (N/U/AC), against the criteria in the table (AK/AL). I also want the second 'match' column (S, AA, AI) to look up scores from three columns the column to the left (Q-R, X-Z, AF-AH), against the outcome/level against the criteria in the table AK/AL.

If ANY (1 or more) of these scores correspond correctly to the criteria given in the tables I would like it to show Y in column H. If none of these do then it would show N. If Y is already in the first match column, it would show Y in the second match column irrespective if those in the second set of scores do not correspond.

Thank you for taking your time to reply

Hi,

I am trying to create something called network units.

To do that I have two separate airport schedules. One for all landings, and one for all departures. I have been trying to figure out how to write a code to identify to generate the possible combinations within a 2 to 6 hour connection. I would be grateful for help if anyone knows how to write such a code. A snapshot of the data is provided below.

If there is data on the left side of hub timing, it means an arriving flight.

If there is data on the left side of hub timing, it means a departing flight.

]]>I am trying to create something called network units.

To do that I have two separate airport schedules. One for all landings, and one for all departures. I have been trying to figure out how to write a code to identify to generate the possible combinations within a 2 to 6 hour connection. I would be grateful for help if anyone knows how to write such a code. A snapshot of the data is provided below.

If there is data on the left side of hub timing, it means an arriving flight.

If there is data on the left side of hub timing, it means a departing flight.

Equip | Op Days | Mkt Al | Flight | Stops | Orig | Dep Time | Hub timing | Arr Time | Dest | Stops | Mkt Al | Flight | Op Days | Equip | Seats | Ops/Week |

789 | 1234567 | EY | 487 | 0 | PER | 1705 | 0035 | 7 | ||||||||

32B | 1234567 | TK | 868 | 0 | IST | 1915 | 0055 | 7 | ||||||||

388 | 1234567 | EY | 20 | 0 | LHR | 1405 | 0120 | 7 | ||||||||

0150 | 0615 | IST | 0 | TK | 869 | 1234567 | 32B | 178 | 7 | |||||||

0205 | 0645 | DUB | 0 | EY | 45 | 123456. | 77W | 412 | 6 | |||||||

0205 | 0645 | DUB | 0 | EY | 45 | ......7 | 77W | 380 | 1 | |||||||

0210 | 0625 | LHR | 0 | BA | 72 | 1234567 | 789 | 216 | 7 |

I try to sort Range B7:I to use as reference E6:E so I don't like to sort A column I am use this Code but I have error plz see attachment .Here is the code but I have errorSub Sort_by_Date()Dim lastrow As Longlastrow = Cells(Rows.Count, 2).End(xlUp).RowRange("B7:I" & lastrow).Sort key1:=Range("E6:E" & lastrow), _ order1:=xlAscending, Header:=xlNo End Sub

Hello dear Excellers!

I'm posting here after hitting a wall with Excel for an awful amount of time, and despite my nature of not giving up easy, I had to post this thread.

**The problem**

I have an imported table which consists of information about sales promotions. What I'm currently looking into and most interested in is the number of weeks each promotion was valid (or active) and also which calendar weeks it is in.

What I'm working with

The way I use to calculate the duration of a sales promotion is fairly simple - I have the starting date and the finish date of the promotion in each row. (1 row = 1 promotion)

**The goal**

However, what makes things a bit more spicy and complicated is the fact that I want to be able to do a Pivot Chart (Clustered column or a Line chart) with every week of the year on the horizontal axis and the count of active promotions for this specific weeks, such as:

I tried with creating some measures, as I guess the solution is most likely hidden somewhere in there, but as you can already guess, I had no success at all. My next attempt was trying to add multiple columns which list every week a promotion was active (some sales promotions last for over a year, so I added 57 columns as a safe margin) and then planned on including them as columns in a pivot chart, but I quickly discovered that the Pivot Chart has a limit of 255 line items (it stops adding columns after the 9th).

I'll be super grateful if someone can give me an idea.

Thank you for your time, guys!

]]>I'm posting here after hitting a wall with Excel for an awful amount of time, and despite my nature of not giving up easy, I had to post this thread.

I have an imported table which consists of information about sales promotions. What I'm currently looking into and most interested in is the number of weeks each promotion was valid (or active) and also which calendar weeks it is in.

What I'm working with

The way I use to calculate the duration of a sales promotion is fairly simple - I have the starting date and the finish date of the promotion in each row. (1 row = 1 promotion)

However, what makes things a bit more spicy and complicated is the fact that I want to be able to do a Pivot Chart (Clustered column or a Line chart) with every week of the year on the horizontal axis and the count of active promotions for this specific weeks, such as:

I tried with creating some measures, as I guess the solution is most likely hidden somewhere in there, but as you can already guess, I had no success at all. My next attempt was trying to add multiple columns which list every week a promotion was active (some sales promotions last for over a year, so I added 57 columns as a safe margin) and then planned on including them as columns in a pivot chart, but I quickly discovered that the Pivot Chart has a limit of 255 line items (it stops adding columns after the 9th).

I'll be super grateful if someone can give me an idea.

Thank you for your time, guys!

May I please get some help with a formula? I got it from a web site, and tried tweaking it, but I'm doing something wrong. So, I'm here asking for assistance.

I want a cell in my spreadsheet to display the total number of "Artists" but without duplicates or empty/blank cells. I found a web page that has a formula that supposedly does this.

https://www.extendoffice.com/documents/excel/2434-excel-count-exclude-duplicates.html#a1

The formula that I'm looking at, on that web page, is this:

=SUM(IF(FREQUENCY(MATCH(A2:A7,A2:A7,0),ROW(A2:A7)-ROW(A2)+1)=1,1))

Please understand that I'm not an Excel expert, and am a novice with formulas. But I did notice that this formula only goes up to row A7. (Unless I am not understanding this formula--which is highly possible.)

Anyway…this spreadsheet is ultimately going to have a few thousand records. So, I redid the formula like this:

=SUM(IF(FREQUENCY(MATCH(A2:A5000,A2:A5000,0),ROW(A2:A5000)-ROW(A2)+1)=1,1))

This didn't work. I'm not 100% sure if this formula ignores blank or empty cells.

So…is this the correct, or best, formula to use for this? If it isn't, where might I find a better one? Thank you very much! Jd

DL Music 2019.xlsm

PS: There is a typo in the subject text. I meant**WITHOUT** Including Duplicates or Blank Cells. My apologies.

I want a cell in my spreadsheet to display the total number of "Artists" but without duplicates or empty/blank cells. I found a web page that has a formula that supposedly does this.

https://www.extendoffice.com/documents/excel/2434-excel-count-exclude-duplicates.html#a1

The formula that I'm looking at, on that web page, is this:

=SUM(IF(FREQUENCY(MATCH(A2:A7,A2:A7,0),ROW(A2:A7)-ROW(A2)+1)=1,1))

Please understand that I'm not an Excel expert, and am a novice with formulas. But I did notice that this formula only goes up to row A7. (Unless I am not understanding this formula--which is highly possible.)

Anyway…this spreadsheet is ultimately going to have a few thousand records. So, I redid the formula like this:

=SUM(IF(FREQUENCY(MATCH(A2:A5000,A2:A5000,0),ROW(A2:A5000)-ROW(A2)+1)=1,1))

This didn't work. I'm not 100% sure if this formula ignores blank or empty cells.

So…is this the correct, or best, formula to use for this? If it isn't, where might I find a better one? Thank you very much! Jd

DL Music 2019.xlsm

PS: There is a typo in the subject text. I meant

Hello guys, I am having some trouble with a formula showing two separate results in one cell. I have attached the excel file so you can easily see what I am trying to do.

I have a lot of physical folders that I have to sort and putnumbers on each one. Then in the excel file you choose thecontent you would like to find from the drop-down menu (for example inC16,C17,C18 "Invoices", "2007", "Mar", respectively) and you getthe number corresponding to the particular folder in another cell (G16).

The formula I used in cell "G16" works when only one folder number corresponds to the particular contents you have chosen. For example, if you choose "Invoices", "2007", "Mar" you get the number 3 in cell "G16". However, when you change "Mar" to "Apr" for example, you should get both number 4 and number 5 (because there are two folders from April - "Apr A-J", "Apr K-J).

Does someone know a way in which I could get both numbers to show up in cell "G16"?

Thank you.

I have a lot of physical folders that I have to sort and putnumbers on each one. Then in the excel file you choose thecontent you would like to find from the drop-down menu (for example inC16,C17,C18 "Invoices", "2007", "Mar", respectively) and you getthe number corresponding to the particular folder in another cell (G16).

The formula I used in cell "G16" works when only one folder number corresponds to the particular contents you have chosen. For example, if you choose "Invoices", "2007", "Mar" you get the number 3 in cell "G16". However, when you change "Mar" to "Apr" for example, you should get both number 4 and number 5 (because there are two folders from April - "Apr A-J", "Apr K-J).

Does someone know a way in which I could get both numbers to show up in cell "G16"?

Thank you.

Добрый день! Подскажите, как разбить текст на столбцы по необходимым критериям! Чтобы каждый необходимый текст был в колонке !! Файл указывает, как именно!

I have been trying to figure out what I am doing wrong with the INDEX function.

What is my goal? Look at a cell, look through the table array in column A and give all the results for multiple columns for anything that matches in column A. I got it to work for 1 column, but when I try to display the second, third, forth column results, it does not work. Below is my image of my sheet

Image:

https://imgur.com/xbElptr

My J2 through J16 has the INDEX command working fine. It is pulling from column B. Now column K I want now pull the value from the next column to the right. On the first results, great it found Arrowbear Lake in column B, but in column K I want to find the same search (J1) but pull from column C. On this example it would pull the value "county".

What do I need to change to make this work. Here is my index value in K2 and I always use CTRL > Shift > Enter.

=IF(ISERROR(INDEX($A$1:$H$16,SMALL(IF($A$1:$A$16=$ J$1,ROW($A$1:$A$16)),ROW(1:1)),2)),"",INDEX($A$1:$ H$16,SMALL(IF($A$1:$A$16=$J$1,ROW($A$1:$A$16)),ROW (1:1)),2))

Any help would be great.

]]>What is my goal? Look at a cell, look through the table array in column A and give all the results for multiple columns for anything that matches in column A. I got it to work for 1 column, but when I try to display the second, third, forth column results, it does not work. Below is my image of my sheet

Image:

https://imgur.com/xbElptr

My J2 through J16 has the INDEX command working fine. It is pulling from column B. Now column K I want now pull the value from the next column to the right. On the first results, great it found Arrowbear Lake in column B, but in column K I want to find the same search (J1) but pull from column C. On this example it would pull the value "county".

What do I need to change to make this work. Here is my index value in K2 and I always use CTRL > Shift > Enter.

=IF(ISERROR(INDEX($A$1:$H$16,SMALL(IF($A$1:$A$16=$ J$1,ROW($A$1:$A$16)),ROW(1:1)),2)),"",INDEX($A$1:$ H$16,SMALL(IF($A$1:$A$16=$J$1,ROW($A$1:$A$16)),ROW (1:1)),2))

Any help would be great.

i would like to classify the duration (subtracting the dates) taken into 4 categories : within 14 days, between 15-29 days, between 30 - 59 days and more than 60 days. If the date is AR is blank, it will take TODAY to subtract G

Appreciate if any guru could help me to further edit the formula

=IFS((IF(AR2="",TODAY(),AR2)-G2)<=14,"Within 14 day",AND((IF(AR2="",TODAY(),AR2)-G2)>14,(IF(AR2="",TODAY(),AR2)-G2)<=29),"between 15-29 days",(IF(AR2="",TODAY(),AR2)-G2)>=30,"30 days and above")

]]>Appreciate if any guru could help me to further edit the formula

=IFS((IF(AR2="",TODAY(),AR2)-G2)<=14,"Within 14 day",AND((IF(AR2="",TODAY(),AR2)-G2)>14,(IF(AR2="",TODAY(),AR2)-G2)<=29),"between 15-29 days",(IF(AR2="",TODAY(),AR2)-G2)>=30,"30 days and above")

Hello gurus,

I'm trying to build a list in which I have column B where I can select from approximately 10 items in an in-cell drop-down list (got that figured out with data validation), and then I want to have another in-cell drop-down list in column C, whose values will be restricted to sub-items based on whatever was selected in column B.

Does that make sense? Is it doable? And if so, how?

Thanks much for any help you can offer!

]]>I'm trying to build a list in which I have column B where I can select from approximately 10 items in an in-cell drop-down list (got that figured out with data validation), and then I want to have another in-cell drop-down list in column C, whose values will be restricted to sub-items based on whatever was selected in column B.

Does that make sense? Is it doable? And if so, how?

Thanks much for any help you can offer!

Hi Forum

I need help with a formula for betting and hoping you guys can help.

I intend to have a starting bet of 10, if it wins, it will be multiplied by the odds of the winning horse/team etc, if it loses, it will be -10 (my stake).

But the next stake will increase by 2. Next bet will be 12, if it wins, it will be multiplied by the odds of the winning horse/team and added to a total. I intend to keep on adding 2 to my stake on each bet until I win, when I win, it will be reset to 10 again.

What sort of formula could I use for this staking plan?

Odds Stake Win/Lose P&L Total P&L +110

6 10 L -10

5 12 L -22

10 14 L -36

6 16 W 96

5 10 W 50

]]>I need help with a formula for betting and hoping you guys can help.

I intend to have a starting bet of 10, if it wins, it will be multiplied by the odds of the winning horse/team etc, if it loses, it will be -10 (my stake).

But the next stake will increase by 2. Next bet will be 12, if it wins, it will be multiplied by the odds of the winning horse/team and added to a total. I intend to keep on adding 2 to my stake on each bet until I win, when I win, it will be reset to 10 again.

What sort of formula could I use for this staking plan?

Odds Stake Win/Lose P&L Total P&L +110

6 10 L -10

5 12 L -22

10 14 L -36

6 16 W 96

5 10 W 50

I lost the correct formula for the last (3rd entry) I was able to choose from a list of "holidays" (up to 3). I can now only get to have the formula choose up (return) to two holidays. Any suggestions how to fix the third formula?

=IF(ISERROR(MATCH(Z1,arr_eventdate,0)),"",INDEX(arr_event,MATCH(Z1,arr_eventdate,0)))

=IF(ISERROR(OFFSET(arr_eventdate,-1+MATCH(Z1,arr_eventdate,0)+MATCH(Z1,OFFSET(arr_eventdate,MATCH(Z1,arr_eventdate,0),0,1000,1),0),-3,1,1)),"",OFFSET(arr_eventdate,-1+MATCH(Z1,arr_eventdate,0)+MATCH(Z1,OFFSET(arr_eventdate,MATCH(Z1,arr_eventdate,0),0,1000,1),0),-3,1,1))

=IF(ISERROR(OFFSET(arr_eventdate,-1+MATCH(Z1,arr_eventdate,0)+MATCH(Z1,OFFSET(arr_eventdate,MATCH(Z1,arr_eventdate,0),0,1000,1),0)+MATCH(Z1,OFFSET(arr_eventdate,MATCH(Z1,arr_eventdate,0)+MATCH(Z1,OFFSET(arr_eventdate,MATCH(Z1,arr_eventdate,0),0,1000,1),0),0,1000,1),0),-3,1,1)),"",OFFSET(arr_eventdate,-1+MATCH(Z1,arr_eventdate,0)+MATCH(Z1,OFFSET(arr_eventdate,MATCH(Z1,arr_eventdate,0),0,1000,1),0)+MATCH(Z1,OFFSET(arr_eventdate,MATCH(Z1,arr_eventdate,0)+MATCH(Z1,OFFSET(arr_eventdate,MATCH(Z1,arr_eventdate,0),0,1000,1),0),0,1000,1),0),-3,1,1))

]]>=IF(ISERROR(MATCH(Z1,arr_eventdate,0)),"",INDEX(arr_event,MATCH(Z1,arr_eventdate,0)))

=IF(ISERROR(OFFSET(arr_eventdate,-1+MATCH(Z1,arr_eventdate,0)+MATCH(Z1,OFFSET(arr_eventdate,MATCH(Z1,arr_eventdate,0),0,1000,1),0),-3,1,1)),"",OFFSET(arr_eventdate,-1+MATCH(Z1,arr_eventdate,0)+MATCH(Z1,OFFSET(arr_eventdate,MATCH(Z1,arr_eventdate,0),0,1000,1),0),-3,1,1))

=IF(ISERROR(OFFSET(arr_eventdate,-1+MATCH(Z1,arr_eventdate,0)+MATCH(Z1,OFFSET(arr_eventdate,MATCH(Z1,arr_eventdate,0),0,1000,1),0)+MATCH(Z1,OFFSET(arr_eventdate,MATCH(Z1,arr_eventdate,0)+MATCH(Z1,OFFSET(arr_eventdate,MATCH(Z1,arr_eventdate,0),0,1000,1),0),0,1000,1),0),-3,1,1)),"",OFFSET(arr_eventdate,-1+MATCH(Z1,arr_eventdate,0)+MATCH(Z1,OFFSET(arr_eventdate,MATCH(Z1,arr_eventdate,0),0,1000,1),0)+MATCH(Z1,OFFSET(arr_eventdate,MATCH(Z1,arr_eventdate,0)+MATCH(Z1,OFFSET(arr_eventdate,MATCH(Z1,arr_eventdate,0),0,1000,1),0),0,1000,1),0),-3,1,1))

Hi all,

I’ve got a conundrum and haven’t been able to find a solution. I’ve just started using excel and don’t know much. Hopefully someone can help!

On one sheet, is a list of all customers. A second sheet has a list of customers with the number of items they’ve purchased.

An example of this is:

Sheet A

John Smith

Amanda Knox

Cindy Rush

Carl Jensen

Andrew Hale

Sheet B

John Smith 1

John Smith 3

John Smith 2

Andrew Hale 4

Amanda Knox 1

Amanda Knox 2

What I want to do is have Sheet A look like this:

Sheet A

John Smith 6

Amanda Knox 3

Cindy Rush (zero=blank)

Carl Jensen (zero=blank)

Andrew Hale 4

I’ve tried using a lot of variations of VLOOKUP, INDEX and MATCH, that but haven’t been able to make a working formula that can go into the entire list of customers on Sheet A while simultaneously leaving the customers who haven’t purchased anything, as is—or at 0.

I was able to use a pivot table to get the sum of customers in Sheet B, but then I still run into the issue of incorporating the pivot table into Sheet A’s list of customers.

Does anyone have any suggestions? If it helps, I’m working with Excel 2016, version 16.16.5 on a Mac.

Thanks for any and all help!

]]>I’ve got a conundrum and haven’t been able to find a solution. I’ve just started using excel and don’t know much. Hopefully someone can help!

On one sheet, is a list of all customers. A second sheet has a list of customers with the number of items they’ve purchased.

An example of this is:

Sheet A

John Smith

Amanda Knox

Cindy Rush

Carl Jensen

Andrew Hale

Sheet B

John Smith 1

John Smith 3

John Smith 2

Andrew Hale 4

Amanda Knox 1

Amanda Knox 2

What I want to do is have Sheet A look like this:

Sheet A

John Smith 6

Amanda Knox 3

Cindy Rush (zero=blank)

Carl Jensen (zero=blank)

Andrew Hale 4

I’ve tried using a lot of variations of VLOOKUP, INDEX and MATCH, that but haven’t been able to make a working formula that can go into the entire list of customers on Sheet A while simultaneously leaving the customers who haven’t purchased anything, as is—or at 0.

I was able to use a pivot table to get the sum of customers in Sheet B, but then I still run into the issue of incorporating the pivot table into Sheet A’s list of customers.

Does anyone have any suggestions? If it helps, I’m working with Excel 2016, version 16.16.5 on a Mac.

Thanks for any and all help!

So if you look at the attached sheet, I want to make some kind of VLOOKUP formula or any formula what would enter the correct category/sub category on the DATA sheet based on if the contents the the description "contain" data in the description column on AutoCat sheet....for example on line 15 on the DATA sheet, it says "BLUE RIDGE COMM CABLE BILL ***********2001" and on the AutoCat Sheet on line 3 it just says Blue Ridge, how could I make the category and sub category automatically fill in on the DATA sheet based on the data in the Auto Cat sheet?

Hi all,

New to the forum. I need a bit of help.

I’m in charge of rostering where I work and I’m looking for a way to calculate a percentage of shifts worked by x amount of staff.

For example....

We have 5 shifts to cover throughout the day. 3 morning, 2 night. The the roster is monthly, every few days the staff change from days to nights and vise versa. How do I calculate of the 5 shifts, how much as a percentage is each staff member working over the month? (Or 6 months etc)

This is purely to figure out if A is working more nights/days than person B, so on and so forth....

Thanks in advance!!

Sent from my iPhone using Tapatalk

]]>New to the forum. I need a bit of help.

I’m in charge of rostering where I work and I’m looking for a way to calculate a percentage of shifts worked by x amount of staff.

For example....

We have 5 shifts to cover throughout the day. 3 morning, 2 night. The the roster is monthly, every few days the staff change from days to nights and vise versa. How do I calculate of the 5 shifts, how much as a percentage is each staff member working over the month? (Or 6 months etc)

This is purely to figure out if A is working more nights/days than person B, so on and so forth....

Thanks in advance!!

Sent from my iPhone using Tapatalk