Let's say I have 4 names of numbers in col A, and 4 names(the same names) in col B.

I want to create a list that contains a combination of the two list in col C.and D I need a macro to do this.:):(

Thanks

I want to create a list that contains a combination of the two list in col C.and D I need a macro to do this.:):(

Thanks

Hello. I have searched everywhere and tried everything I know but cant seem to get this.

I have daily data over many years. I would like to average the all of one weekday in the same month. For example, All Mondays in November over the course of many years.

It is laid out like this:

...**A**............**B.............C**...................... **BR**..............**BS**

**1** DATE.......11/02/09...11/03/09.............02/14/16.....02/15/16

**2 ** VALUE......2.97.........3.45...................4.9.............1.9

So using the date row I would like to average the Value row for specific days in specific months.

Thanks so much!!!!!!!!!!!!

EDIT: I APOLOGIZE FOR MY LAZINESS TO NOT CORRECT SPELLING IN THE THREAD TITLE. I WOULD EDIT OR DELETE AND RE-POST IF I COULD

]]>I have daily data over many years. I would like to average the all of one weekday in the same month. For example, All Mondays in November over the course of many years.

It is laid out like this:

...

So using the date row I would like to average the Value row for specific days in specific months.

Thanks so much!!!!!!!!!!!!

EDIT: I APOLOGIZE FOR MY LAZINESS TO NOT CORRECT SPELLING IN THE THREAD TITLE. I WOULD EDIT OR DELETE AND RE-POST IF I COULD

All;

I am trying to come up with a formula that will sum all the numbers in column I (“net”) per day, in column E.

For example, it sums all values on 1/19/17, 1/20/17, etc.

I’m sure there is a way, I seem to struggle with these “running” formulas. Sample attached.

Maybe I'm trying to skin the cat the wrong way; but I want a formula I can run to show total amount of weight picked up (column I) per day (column E).

Thanks ahead.

I am trying to come up with a formula that will sum all the numbers in column I (“net”) per day, in column E.

For example, it sums all values on 1/19/17, 1/20/17, etc.

I’m sure there is a way, I seem to struggle with these “running” formulas. Sample attached.

Maybe I'm trying to skin the cat the wrong way; but I want a formula I can run to show total amount of weight picked up (column I) per day (column E).

Thanks ahead.

Hi All,

Total perturbed at this and don't even know what to shows specifically.

I have a MAIN macro that calls a SUB macro.

When I run the MAIN everything is fine. Then I run the SUB separately from the MAIN everything is fine.

However, when I CALL the SUB from the MAIN, I get "Run-Time error '1004': AutoFilter method of Range class failed"

Any Ideas?

Thanks!

Ed

]]>Total perturbed at this and don't even know what to shows specifically.

I have a MAIN macro that calls a SUB macro.

When I run the MAIN everything is fine. Then I run the SUB separately from the MAIN everything is fine.

However, when I CALL the SUB from the MAIN, I get "Run-Time error '1004': AutoFilter method of Range class failed"

Any Ideas?

Thanks!

Ed

Hello friends,

Please someone help!

I enter some numbers in column F on sheet1. These numbers must be transfered to column B in sheet2. I then delete the numbers on sheet1 and enter some new numbers in the same column(F). What I want is that, after entering the new numbers on sheet 1, these numbers must be transfered to sheet2 column B, below the previous numbers, delete numbers on sheet1,transfered to sheet2, below previous numbers,etc.etc

]]>Please someone help!

I enter some numbers in column F on sheet1. These numbers must be transfered to column B in sheet2. I then delete the numbers on sheet1 and enter some new numbers in the same column(F). What I want is that, after entering the new numbers on sheet 1, these numbers must be transfered to sheet2 column B, below the previous numbers, delete numbers on sheet1,transfered to sheet2, below previous numbers,etc.etc

The following code works if the selected cell in within the first named range (columns B:J) but if I try putting the activecell in the next named range (columns K:S) an error occurs "object variable or with block variable not set". What I am trying to do is determine which named range the selected cell is in. If it isn't in any of the 5 named ranges then execute the code section that the activecell isn't in any of the 5 specified named ranges. Why does it work in the first case if the activecell in in that range but fails if I try putting the active cell within the next named range. I am doing entire columns for named ranges so Line1Cols are B:J, Line2Cols is defined as K:S and so on.

]]>Code:

`Sub test2()`

Dim isect, curCell As Range

Worksheets("INPUT-PRINT").Activate

Set curCell = Range(ActiveCell.Address)

Select Case curCell

Case Application.Intersect(Range("Line1Cols"), Range(ActiveCell.Address))

Debug.Print "Column 1"

Case Application.Intersect(Range("Line2Cols"), Range(ActiveCell.Address))

Debug.Print "Column 2"

Case Application.Intersect(Range("Line3Cols"), Range(ActiveCell.Address))

Debug.Print "Column 3"

Case Application.Intersect(Range("Line4Cols"), Range(ActiveCell.Address))

Debug.Print "Column 4"

Case Application.Intersect(Range("Line5Cols"), Range(ActiveCell.Address))

Debug.Print "Column 5"

Case Else

Debug.Print "Not in desired columns"

End Select

End Sub

At my job we have a nice recipe book that gives information about an article by entering the article number in the right place.

This information is presented by VLOOKUP functions. So if you type in the article number the VLOOKUP functions give the right information of that article.

What I want to do is print the information of several articles that are planned to be produced in one week.

So basically I want to print the article information of all the articles that we produce in one week by one click.

The articles that we produce in one week is available in an excel file.

Can you help me?

]]>This information is presented by VLOOKUP functions. So if you type in the article number the VLOOKUP functions give the right information of that article.

What I want to do is print the information of several articles that are planned to be produced in one week.

So basically I want to print the article information of all the articles that we produce in one week by one click.

The articles that we produce in one week is available in an excel file.

Can you help me?

sir

i am Add the vba code for my excel sheet sir i am send the excel sheets "for example open the excel sheet & click on the home command button the main menu user form was display than i am move the mouse to company info the company info fram was display and click on the create company the create company userform was display, click on the select company the select company userform was display....."

thanking you sir

i am Add the vba code for my excel sheet sir i am send the excel sheets "for example open the excel sheet & click on the home command button the main menu user form was display than i am move the mouse to company info the company info fram was display and click on the create company the create company userform was display, click on the select company the select company userform was display....."

thanking you sir

I have a Table with dates in column A and then column B there is a dollar amount. Column C and D have drop down categories and subcategories. I know how to add up the columns/rows, based on the category chosen. But now I need to add a date factor to my equation. So posted below is my current formula. But I need it to only pull the numbers from the rows with dates before July 1st 2016 and or after July 1st 2016.

=SUMIF(Payrollregister[Subcatagory],'Fixed Data'!Q3,Payrollregister[Withdrawal])+SUMIF(FixedExpenses[Subcatagory],'Fixed Data'!Q3,FixedExpenses[Withdrawal])+SUMIF(COB[Subcatagory],'Fixed Data'!Q3,COB[Withdrawal])

Thank you for any help!

Brenda

]]>=SUMIF(Payrollregister[Subcatagory],'Fixed Data'!Q3,Payrollregister[Withdrawal])+SUMIF(FixedExpenses[Subcatagory],'Fixed Data'!Q3,FixedExpenses[Withdrawal])+SUMIF(COB[Subcatagory],'Fixed Data'!Q3,COB[Withdrawal])

Thank you for any help!

Brenda

Hello all,

I am new to the forum and excel as a whole and I am experiencing issues formating a large sets of data in excel. I was wondering if anyone could help me by giving me an idea or explain why certain things are not working.

I am currently trying to format a 200 000 rows excel file with the format:

Product level 1 Pruduct leve 2 Product level 3 Country Customer Date Units Sales

The names of the customers and products are in gibberish and one of the tasks is to assign them a name such as Client 1, Client 2.... Product 1, Product 2... etc., thus making it clearer to understand. This is where I have stumbled upon a problem because I have no idea how to replace the names of all Clients. What I did so far is to copy all the clients, paste them in a new sheet, remove duplicates to show 5798 unique clients and tried to write Client 1, Client 2... next to their names with the idea to later find how I can substitute all of them at once. But now that I have removed the duplicates, the numbers counting the rows are blue and the autofill function is not working( I cannot extend the Client 1, Client 2 cells down to fill them with Client 3, Client 4 and etc.). Also I have no idea if this is the right thing to do at all because even if I manage to do it, I dont now how to further proceed with the substitution of the names.

The next task is to prepare tables to better show the results, but I have no idea what that Product Level is as it contains different "products" in it.

The third task is to make an ABC analysis - to devide all customers in 3 major groups, one representing 50% of all sales, one representing 30% and one 20%. This I thought of doing by finding the average sales and with an if function to find all values above 30% of the average and put them in category A, else if value is < than 30% but > than 20% to go to category B and else go to category C. But I have no idea if it can work in excel like that (with 2 else conditions that contain an if as well).

The task is part of a course at work I am taking to increase my level of competence so it is quite important for me to be able to do these kind of functions.

I do hope someone is willing to help me out here and give me their insight on the topic.

]]>I am new to the forum and excel as a whole and I am experiencing issues formating a large sets of data in excel. I was wondering if anyone could help me by giving me an idea or explain why certain things are not working.

I am currently trying to format a 200 000 rows excel file with the format:

Product level 1 Pruduct leve 2 Product level 3 Country Customer Date Units Sales

The names of the customers and products are in gibberish and one of the tasks is to assign them a name such as Client 1, Client 2.... Product 1, Product 2... etc., thus making it clearer to understand. This is where I have stumbled upon a problem because I have no idea how to replace the names of all Clients. What I did so far is to copy all the clients, paste them in a new sheet, remove duplicates to show 5798 unique clients and tried to write Client 1, Client 2... next to their names with the idea to later find how I can substitute all of them at once. But now that I have removed the duplicates, the numbers counting the rows are blue and the autofill function is not working( I cannot extend the Client 1, Client 2 cells down to fill them with Client 3, Client 4 and etc.). Also I have no idea if this is the right thing to do at all because even if I manage to do it, I dont now how to further proceed with the substitution of the names.

The next task is to prepare tables to better show the results, but I have no idea what that Product Level is as it contains different "products" in it.

The third task is to make an ABC analysis - to devide all customers in 3 major groups, one representing 50% of all sales, one representing 30% and one 20%. This I thought of doing by finding the average sales and with an if function to find all values above 30% of the average and put them in category A, else if value is < than 30% but > than 20% to go to category B and else go to category C. But I have no idea if it can work in excel like that (with 2 else conditions that contain an if as well).

The task is part of a course at work I am taking to increase my level of competence so it is quite important for me to be able to do these kind of functions.

I do hope someone is willing to help me out here and give me their insight on the topic.

Hello mate,

Hope you will help me for sure. I am new user of Excel. I don't know how to add Macro onto the sheet. Please guide me a little.

Thank you!

]]>Hope you will help me for sure. I am new user of Excel. I don't know how to add Macro onto the sheet. Please guide me a little.

Thank you!

Sub DD()

Dim celldata As String

celldata = ""

For j = 2 To 9

celldata = celldata + Cells(3, j) + " "

Next

End Sub

'''value in the 3rd row is 2,4,bi,33,33,55,56,777,ss

]]>Dim celldata As String

celldata = ""

For j = 2 To 9

celldata = celldata + Cells(3, j) + " "

Next

End Sub

'''value in the 3rd row is 2,4,bi,33,33,55,56,777,ss

Hello all, first time on this board :)

I have this draw down calculation which works out the draw down based on numeric values stored in column E, SPY Close

=MIN(0,(E34-MAX($E$2:E34))/MAX($E$2:E34))

This will calculate the maximum draw down based on the SPY close (Column E) values...... (non % values)

I now want to do this same result with a % series of numbers.

I have attached a screen shot to show what I am trying to achieve:

https://2.bp.blogspot.com/-6RrCOaPpW.../image%2B1.png

This shows in a chart, the out put of the current formula: =MIN(0,(E34-MAX($E$2:E34))/MAX($E$2:E34))

I just need to do the same with the BE, Cumulative % return column.

Really appreciate any insight into this one!

Thanks!

Andrew

]]>I have this draw down calculation which works out the draw down based on numeric values stored in column E, SPY Close

=MIN(0,(E34-MAX($E$2:E34))/MAX($E$2:E34))

This will calculate the maximum draw down based on the SPY close (Column E) values...... (non % values)

I now want to do this same result with a % series of numbers.

I have attached a screen shot to show what I am trying to achieve:

https://2.bp.blogspot.com/-6RrCOaPpW.../image%2B1.png

This shows in a chart, the out put of the current formula: =MIN(0,(E34-MAX($E$2:E34))/MAX($E$2:E34))

I just need to do the same with the BE, Cumulative % return column.

Really appreciate any insight into this one!

Thanks!

Andrew

Hi dear Experts,

I have a list of drivers that are working in either one school or two. These schools have different starting time either 7:00 or 8:00.

Now I can easily extract School Names in the 'First School' and 'Second School' according their timings by combining multiple lookup value and lookup array using CTRL+Shift+Enter.

But Here my only Issue is that I want the School names in the 'First School' if the related driver has only one school. It doesn't mater if starting time is 8:00.

Please help me solve this.

Thank you.

I have a list of drivers that are working in either one school or two. These schools have different starting time either 7:00 or 8:00.

Now I can easily extract School Names in the 'First School' and 'Second School' according their timings by combining multiple lookup value and lookup array using CTRL+Shift+Enter.

But Here my only Issue is that I want the School names in the 'First School' if the related driver has only one school. It doesn't mater if starting time is 8:00.

Please help me solve this.

Thank you.

G'day,

I hope I can articulate this one. Apologies, this is my first post.

I have a set of data within the range B3:K13 which includes random numbers under 10 using =RAND()*10.

I have created 4 named ranges (where A3 is blank):

Dates: A3:A13 (Numbered 1 - 10)

Dates1:A4:A13

Timesteps: B3:K3 (Dated 1/01/2016 - 10/01/2016)

Timesteps1: A3:K3

So I have 4 criteria:

Start Date:B25 (Dropdown List "Dates")

End Date: B26 (Dropdown List "Dates")

Start Timestep: B27 (Dropdown List "Timesteps")

End Timestep: B28 (Dropdown List "Timesteps")

There are two formulas I have used that can generate the answers I need.

=SUM(OFFSET($A$3,MATCH($B$25,Dates,0),MATCH($B$27,Timesteps,0),MATCH($B$26,Dates1,0)-MATCH($B$25,Dates,0),MATCH($B$28,Timesteps1,0)-MATCH($B$27,Timesteps,0)))

=SUM(INDEX($B$4:$K$13,MATCH($B$25,Dates,0),MATCH($B$27,Timesteps,0)):INDEX($B$4:$K$13,MATCH($B$26,Dates,0),MATCH($B$28,Timesteps,0)))

Everything is good so far however I want add another criteria to this formula.

I want to create a new formula that will still sum the dynamic ranges not only for the selected range (All week) but for weekends and weekdays as well within that selected range.

I tried this formula but it didn't work.

=SUM(IF($N$4:$N$13>=6,OFFSET($A$3,MATCH($B$25,Dates,0),MATCH($B$27,Timesteps,0),MATCH($B$26,Dates1,0)-MATCH($B$25,Dates,0),MATCH($B$28,Timesteps1,0)-MATCH($B$27,Timesteps,0)),0))

I have created a work around in order to get there =SUMIFS(P4:P13,Dates,">="&$B$25,Dates,"<="&$B$26). But I need it in one formula!

I know this is a tricky one but if anyone could help me out it would be much appreciated.

I got the idea from Excelisfun's EMT 1071 - https://www.youtube.com/watch?v=lh4VjEoD5WY

Here is a screenshot of my spreadsheet - https://postimg.org/image/c455h66in/

Cheers!

]]>I hope I can articulate this one. Apologies, this is my first post.

I have a set of data within the range B3:K13 which includes random numbers under 10 using =RAND()*10.

I have created 4 named ranges (where A3 is blank):

Dates: A3:A13 (Numbered 1 - 10)

Dates1:A4:A13

Timesteps: B3:K3 (Dated 1/01/2016 - 10/01/2016)

Timesteps1: A3:K3

So I have 4 criteria:

Start Date:B25 (Dropdown List "Dates")

End Date: B26 (Dropdown List "Dates")

Start Timestep: B27 (Dropdown List "Timesteps")

End Timestep: B28 (Dropdown List "Timesteps")

There are two formulas I have used that can generate the answers I need.

=SUM(OFFSET($A$3,MATCH($B$25,Dates,0),MATCH($B$27,Timesteps,0),MATCH($B$26,Dates1,0)-MATCH($B$25,Dates,0),MATCH($B$28,Timesteps1,0)-MATCH($B$27,Timesteps,0)))

=SUM(INDEX($B$4:$K$13,MATCH($B$25,Dates,0),MATCH($B$27,Timesteps,0)):INDEX($B$4:$K$13,MATCH($B$26,Dates,0),MATCH($B$28,Timesteps,0)))

Everything is good so far however I want add another criteria to this formula.

I want to create a new formula that will still sum the dynamic ranges not only for the selected range (All week) but for weekends and weekdays as well within that selected range.

I tried this formula but it didn't work.

=SUM(IF($N$4:$N$13>=6,OFFSET($A$3,MATCH($B$25,Dates,0),MATCH($B$27,Timesteps,0),MATCH($B$26,Dates1,0)-MATCH($B$25,Dates,0),MATCH($B$28,Timesteps1,0)-MATCH($B$27,Timesteps,0)),0))

I have created a work around in order to get there =SUMIFS(P4:P13,Dates,">="&$B$25,Dates,"<="&$B$26). But I need it in one formula!

I know this is a tricky one but if anyone could help me out it would be much appreciated.

I got the idea from Excelisfun's EMT 1071 - https://www.youtube.com/watch?v=lh4VjEoD5WY

Here is a screenshot of my spreadsheet - https://postimg.org/image/c455h66in/

Cheers!