I got a matrix with on top a row of names of persons.
On the column left of the matrix dates are given.
In between are codes.
I am looking for a formula that collects all persons that have one of the codes given in cells L5:L8 on a particular date which is given in cell L9
The list will start at L11 downwards.
What kind of formula do I need that looks for a date and find all the names that have the same character on that particular day
In the example below cell R4 kooks for date R3 in range A1:L15 and find all cells that have a "G" and displays all the corresponding names from C1:L1
I prefer non array-formulas
I have two vertical ranges that I need summarized into 2 adjacent vertical ranges.
"
A B C D
| SUMMARY
model qty| modelqty
1 4.12922.0000| 4.12952.2000
2 2.000012.1250| 2.000025.1250
3 4.12929.0000| 318.0000
4 318.0000|
5 4.1291.2000|
6 213.0000|
"
A1:A6 is my SKU's model number B1:B6 is my inventory C1:C6 should contain formulas that result in a summary of the models D1:D6 should contain formulas that result in a sum of the inventory count for each model
how do you create a graph with a field such as vendors on the X-axis, but with dollar values spent on the left side of the y-axis and percentage of total dollars spent on the right side vertical axis?
View 3 Replies View RelatedI need more vertical cells. I have a very large spreadsheet. I need about 75,000 vertical cells, The spreadsheet becomes full at around 65,000 cells.
View 9 Replies View RelatedTrying to build a form that helps assemble parts a pieces based on filling out just a small section of information. Here just a short example:
Tower Type: Type 1
Coax Length: 100 ft
Based on the above criteria, I am going to have a line item that auto fills specific parts and pieces based on the answer. The main problem I have is the coax length answer will fit within several ranges and I need a cell to be able to identify and autofill ALL of those ranges.
Specifically, if the coax length is between 0 and 100, I need to autofill the below cell with Coax 1, from 101 to 200 Coax 2, and so on and so forth. Lets say I have 5 ranges of "greater than, less than" values. how do I make a formula that will consider all of these in a VLOOKUP scenario?
If I haven't asked the right questions, or phrased them incorrectly, please let me know....I'm not the most excel experienced person...hence my visit to this forum!
I want to be able to have a progressbar that populates upwards. When I use a scrollbar to adjust the height it wants to go down.
View 2 Replies View RelatedI have attached an excel sheet with the input and output required.
It is basically concatenation vertically with certain tags.
Is there a method by which i can achieve this. MACRO OR FORMULA??
Book_test.xls
My spreadsheet has 459 rows; however, the vertical scroll bar ends at row 569738. I have attempted to delete all of the extra rows by holding SHIFT and CTRL, striking the down arrow then right-clicking on the left margin of highlighted row numbers and choosing delete. I have also done the same procedure except clicking clear all from the editing menu. None of these methods has removed the rows and enlarged the vertical scroll bar to a proportionate size in relation to the number of rows.
View 14 Replies View RelatedI have a worksheet which find a cell in a sheet using VBA and then print the value of this cell using the range.printout method.
Works great but now I want to print 2 cells (let's say A1 & B1) but I want the values to be printed below each other instead of next to each other.
So A1 should be printed on the first line and B1 on the second line. When I use Range(A1:B1).printout the values are on 1 line. How can I let them print each value on a new line ???
I was wondering if it's possible to create a macro that would check the range horizontally and vertically and return the resoults in a userform so that when workbook is open this userform would come up with the information who is due for refresher....
The sample book doesn't show the real size of the original matrix which has 190 employess and about 30 procedures so as you can imagine it's a pain.. to keep it up to date so I need something to flag that for me, yes I can see the red cells but still there's so many cells that I might easly miss some of them....
I use this code below to run horizontal progress bar. is there any code how to make Vertical Progress Bar,?
[Code] .....
I have data in a vertical column how can i equate that data to a horizontal row in other sheet. how to do it by formula?
View 4 Replies View RelatedI need to pull up the invoiced revenue by customer and month into Spreadsheet 1 from Spreadsheet 2, vlookup wont work and nested index and match wont work.
Spreadsheet1
Spreadsheet2
I need to create a multipage with tabs running down the left hand side. I cannot use the normal horizontal multipage control because this would result in an impossibly wide page. Is there a vertical multipage control? I could not find one? Or a way to convert the multipage control to show vertical tabs?
Please bare in mind that this vertical multipage will be shown in a form that already includes a normal horizontal multipage (I am using multipages within multipages too!).
Im am trying to create a search marco button that allows me to search in multiple worksheets in one work book. I came across this CODE the first part of it works. It pops open user input box and ask for the word that i would like to search but the this error message pops up Runtime error1004 Method 'range" of object'_Global'failed and i dont know what to do
Private Sub SearchButton_Click()
SearchString = InputBox("Enter Search String", "Search")
If SearchString = "" Then Exit Sub
For Each c In Range(myRange)
If InStr(LCase(CStr(c)), LCase(SearchString)) Then
[Code]....
I have not found a suitable solution for arranging vertical data horizontally using VBA
I have number of data in column B arranged vertically in sheet1 how can we arrange the data in sheet 2 column B row 3 horizontally.
The vertical data keeps increasing when new data is added, so VBA code should search to the last line in Sheet1 column B.
All duplicate entries should not be repeated when arranging horizontally.
See attached for a clearer view : LOOKUP SEARCH.xlsx
View 10 Replies View RelatedI've been working on this for days, i still got no solution on what formulas I will put on cells in "To" tab. The excel file is composed of two tabs "To" and "From". We will convert the "From Tab" to "To" format. What formula will I put in D3 to V3 and drag down in "To" tab to convert datas from "From" tab.
View 9 Replies View RelatedI have to fill an invoice template, whereby I need to lookup the unit price in a pricing sheet, based on the effective date (horizontally placed) and the specific tasks to be invoiced (vertically placed).
The effective date cell (B1) is the one that can be changed, the formulas in the unit price cells (yellow highlighted) then needs to look up the price for that specific task in the table on the tab "pricing sheet".
I was trying something with INDEX / MATCH, but can't get it to work.
I've got the following complex issue:
...................Trader1 Trader 2 Trader 3 Trader 4 Trader5... Trader20 (Horizontal)
Commodity1 .....0.....2.....0.....1.....1
Commodity2 .....0.....4.....0.....-3.....0
Commodity3 .....1.....0.....0.....5.....0
Commodity4 .....0.....0.....2.....0.....0
.
.
.
Commodity 36 .....2.....1.....1.....0.....0
I have 5 cells that are basically a drop down list of all the traders, so the user can select up to 5 traders.
Once the user selects up to 5 traders (lets say Trader 1, 3 and 5 for our example) I have the following requirements to fulfill:
Displaying all commodities that have non-zero, non-negative values associated with those traders in a list somewhere.
For our example, this would then display:
Commodity 3
Commodity 4
Commodity 1
I have a very large Spreadsheet (time Sheet Report) that is very messy, how to reorganize the data properly.
We have employees that worked multiple shifts and our system record all their punches. However the system list the data in a vertical position, but I need it to be display horizontally. But there is another problem, there is some employees that did not punch for lunch and would like to add the 30 minutes lunch for only those that worked 8 hours shift.
Attached is a sample : Sample.xls
I have a problem that lookup vertical and horizontal corresponding values when there was duplicate values as it's only returning the first value found. What I want was to lookup the vertical and horizontal corresponding values on the left most & top most column based on the largest values column and also to return the duplicate values under the vertical and horizontal value column in ascending order if it's a duplicate values.
View 4 Replies View RelatedI'm trying to create an area chart to show the change in the number of staff working in my store over time. Unfortunately, Excel likes to draw diagonal lines. That is if I have the following data:
9:00AM - 1 staff
10:00AM - 2 staff
11:00AM - 1 staff
Excel will draw a diagonal line between each data point, rather than creating a box. This gives the illusion that there are somehow half people working at 9:30 and 10:30. Is there a workaround?
I would like to be able to use autofill from a vertical range to a horozontal one. In the example, if you click and drag on cell "R3" (=M3) it samples to cell dirrectly below it "M4", when I want it to sample the cell M6, M9 ect,ect. Is there a way to program the autofill to skip cells?
View 2 Replies View Relatedi have a data in columns and would like to convert into rows
e.g.
11 A B C D E F G H
22 1 2 3 4 5 6 7 8
33 2 3 4 5 6 7 8 9
44 3 4 5 6 7 8 9 0
and result should be
A 22 1
A 33 2
A 44 3
B 22 2
B 33 3
B 44 4
C 22 3
C 33 4
C 44 5
and so on ..
I have a list with entries in one column like this:
sourceText
empty cell
targettext
sourceText2
category2
targettext 2
and I want to change it like this:
sourceTextempty cell target text
sourceText2category2target text 2
Attached sample data. Sheet one as it is. Sheet 2 as I want them to be (I don't need any of the formatting in Sheet 2).
I have a table where I need to return a value according to THREE conditions:
1. Vertical, which is a shop code for instance
2. And 2 horizontal:
- One: segment (heading level 1)
- Two: product (heading level 2)
E.g. for "Shop1", what's the value for segment "PI" and product "Credit", etc.
An example file attached : fz73m5zEmZS6Y14-C.xlsx
The key thing for me is to do this WITHOUT changing data structure, because that really would be a huge pain. I mean, if it's no possible to do without changes (e.g. adding an extra column, etc.), I'll have to live with that I guess. But ideally I would really like to avoid changing data structure.
I have a huge data table, and I just can't seem to find a way to sum the numbers the way I want.
Here's an example of the table:
___ A B C A B C A B C A B C A B C
Jan 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5
Jan 3 4 5 6 3 4 5 6 3 4 5 6 3 4 5
Jan 3 4 5 6 3 4 5 6 3 4 5 6 3 4 5
Jan 3 4 5 6 3 4 5 6 3 4 5 6 3 4 5
Feb 3 4 5 6 3 4 5 6 3 4 5 6 3 4 5
Feb 6 3 4 5 6 3 6 3 4 5 6 3 6 3 4
Feb 6 3 4 5 6 3 6 3 4 5 6 3 6 3 4
Feb 6 3 4 5 6 3 6 3 4 5 6 3 6 3 4
Feb 6 3 4 5 6 3 6 3 4 5 6 3 6 3 4
Mar 6 3 4 5 6 3 6 3 4 5 6 3 6 3 4
Mar 6 3 4 5 6 3 6 3 4 5 6 3 6 3 4
...
...
But, in essence, I need to sum together every value that's in January AND is in a column labeled "A".
I could just write up a bunch of "sumif=" functions but I feel like there's a simpler way.. I tried using "sumifs=" but I don't think it can handle horizontal AND vertical criteria...
I have the attached worksheet that I would like to know how to move the data from Vertical to Horizontal
Is there a set function to do this or does it require a macro?