I am trying to creatre a formula which list numbers from rows to columns by deleting the vertical spaces i.e.
A1 lists empty
A2 lists 3
A3 lists empty
A4 lists 7
A5 lists 1
A6 lists empty
result:
Columns: B C D E F G
3 7 1
similar to stacking values into a nice hoz column and not noteing any spaces
In the attached spreadsheet I would like to create a list of part numbers if column D E & F all have a #N/A value. Right now I'm using a True/False but this list contains around 3000 items and I don't want to have to scroll down to find the True values. Is there a way I can just create a list of corresponding part numbers to the True Values?
SAMPLE.xlsx
I am creating a template for formatting data that I download from a wholesaler to upload to a platform. The large database of around 1300 records that comes with category and subcategory names, among all the rest of the data, that I download There are more column titles and information than I need, so my template will have blank headings that I will know to simply delete after I paste. Maybe there is a Macro for that too.
The category and subcategory names to appear in certain columns. I want those names to appear in multiple columns within the same records. How to create a formula/macro to copy all of the data in one column to a different column with a different heading and keep those headings different and save them within my template. I have a list of the 240 with CategoryID, ParentID, Category and Subcategory all corresponding. These always come and go in CSV format, so I cannot create additional sheets to save within my template without a hassle.
In two ID number value columns, I would like all of the text names replaced with corresponding numbers for upload to the platform that requires them. I have been trying to figure out the best way to create a macro to save within my template for daily use, but so far I have not been able to figure it out. I have been watching videos on vlookup, index & match, find & replace...I have not seen what I need just yet
OK, SO HERE IS MY PRODUCT TEMPLATE SO FAR: [URL]
I'm afraid I've run into some trouble with the Listbox function, which I really love, but I can't quite get past this hurdle.
I want to have a listbox which populates by checking an entire column for values, ignoring blank cells, and, if value is present, to also check to see if any value is present in the cell one column to the right and one row down from the cell that has the initial value. If both these conditions are true, to then display both values in the Listbox. In other words, the Listbox would contain two columns.
During this routine, when checking value is present in the second cell, I'm guessing that an IF statement would increment a variable signifying the cell reference by + 1 for column and + 1 for the one row down?
I have a spreadsheet with about 300 products listed in column A. In row 1, I have 30 store listed across. This produces a grid of data with sales for the past 3 months. I would like to easily have a list of the product and store that had 0 sales. I have tried a combination of INDEX/MATCH as an array formula but can't get it to work.
View 6 Replies View RelatedHow can I create a Makro that looks for values (e.g. values > 90) and that then generates a list with: all the values greater than 90, the row names and column names of the values?
I am pretty sure I will need a Makro for this, but how to create it. I am using excel 2010.
I have attached a file to give you an example. The red table is the table with the values. The blue table is what I want to be generated automatically. In the example I did it manually.
Workbook1.xlsx
I have an issue with being able to process my data i have a text file in notepad that lists a massive amount of values (enough for excel to process if they are listed down a column but not across a row) each separated by a space. As an example here is a small portion of the data.
81768102 191193210 386225426 110858190 393958997 21773704 22450052 70617438 843133051 103582830 370163346 819494826 109538724 846339187 19638405 50748904 476397524 128490548 134215188 252862729 387318907 82658728 15822910 199255054 172623979 59872284 773581712 124854321 547098635 604524102 45265054 203132867 225629848 215828319 14779508 300950341 715797961 329121584 366323012 583555062 917794380 216847744 784432795 606179111 537865871 500392632 37701513 830010548
I know how to import this as text but i can only wither get it all in one cell or across the 1st row. The first row can only display about 16000 values and that isn't enough for my end product to be accurate enough.
So is there any way to import the text file and have it formatted so a each new value has its own row? For example...
81768102
191193210
386225426
110858190
393958997
21773704
Or is there anyway to get the data from my already imported giant A1 cell into the above format?
Added an example of how it looks and how i want it to look. This is how it looks when i open the txt file containing my data in a way that all values stay in the excel sheet. As well as this there is an example of how i want it or rather need it to look.
In A1 of Sheet1!, I need a formula that lists and sorts all the 'unique' values from range
ECWP!$O$6:$Q$1505
I have a worksheet (say, Sheet1) that I am going to manually import a large data set into on a weekly basis for reporting purposes. One of the columns from this data dump will have the header "Design Id" in the first row, but it may not be in the same column every time. I am trying to figure out how to create dropdown lists on a separate worksheet (say, Sheet2) in the same workbook where the data source always looks up the column containing the header "Design Id" from Sheet1 and then returns only the unique values from that column as options in the list.
View 1 Replies View RelatedI have 6 worksheets in my file. In sheets 1-5, column A2:A26 list people's names. Some people's names appears on more than one sheet. Not all cells are populated with a value.
ex.
SHEET1
COLUMNA
Bill
[Code]....
My attempt was... =INDEX('Week1:Week5-!$A$2:$A$26,MATCH(0,COUNTIF($A$1:A1,"Week1:Week5"!$A$2:$A$26),0))
where the sheets were Week1-Week5 and the values on each sheet was A2:A26. But I think there's an issue with Excel being able to 3D reference for these types of functions.
I am using Excel 2010. I need to copy a list from any Column on the right to the existing Column. See the sample file.
View 10 Replies View RelatedI'm trying to use data validation to restrict the user to only selecting values in a list which I create. Right now, the list is a named range. I'd like to get rid of the range and just use a named list. I create a name using the following as my list.
Insert > Name > Create
Name: Fruit
Refers to:
banana,apple,orange
When I try to use the name Fruit in my data validation, I get the message "The List Source must be a delimited list, or a reference to single row or column." I thought my name "fruit" was a delimited list.
The function below should return the value in a cell IF the string in the cell two cells to the left of it is "Nemo" Omitting the part highlighted in blue, my function returns a list of unique values...but when it's included, nothing is returned at all. Perhaps, there is an error in the syntax that I'm overlooking?
Function listUnique(rng As Range) As Variant
Dim row As Range
Dim elements() As String
[Code]....
[URL]
I have Column C and D filled with numeric temperature and humidity data. I need next 16 rows (8 more set) to be filled with randomly generated dummy data by the values exist in C and D. Same numbers but randomly distributed.
There should be a formula to extract the largest values and its corresponding values. For example, if column
Products Costs
A1 Pen 200
A2 Pencil 125
A3 Radio 670
A4 Apple 1500
A5 Xbox 222
A6 TV 100
The desired outcome
Products Cost
Apple 1500
Radio 670
Xbox 222
Pen 200
Pencil 125
TV 100
The second goal is extracting the top 3 largest values using a formula.
Sample file is attached for your convenience. Desired outcome is in GREEN.
Below the two columns represent Column A (date and time) and Column B is temperature.
How can I take the mean of all temperature values for May 1, then take the mean of all temperature values for May 2, etc.?
5/1/2013 0:0012.4
5/1/2013 1:0012.4
5/1/2013 2:0012.5
5/1/2013 3:0012.5
5/1/2013 4:0012.5
5/1/2013 5:0012.4
5/1/2013 6:0012.4
I have two columns in excel, col A and col B.What I would want to achieve is following:
Col A Col B
A 2
A 2
A 2
B 3
B 3
B 3
B 3
C 3
C 3
C 3
Now I want a total of values in column B pertaining to unique (or say 1st occurences) of values in Col A. So in this case the output should be 8 ,Achieved as follows(2 +3+3).
[URL]
Here is a table
Col1 Col2 Col3 Col4
8821015m14:44.0E__uMail
9266321m34:13.0E__uMail
92677165m23:43.0E__uMail
9309415m07:24.0E__uMail
[Code] .....
Here is some context
Each one of these rows represents an entry into a ticket in our incident management system. Col4 represents the method by which the incident was reported to us (Email, Voicemail, Walk In, Ask IT). A ticket can be open and closed with a single entry if the issue is easily solved or it can have many entries if the issue is complex and requires more troubleshooting or escalation to a higher tier of support. I need to determine the number of unique tickets submitted by each reporting avenue (Email, Voicemail, Walk In, Ask IT).
In this table the number of tickets that were:
submitted via Email is 14
submitted via Voicemail is 1
submitted via Walk In is 2
submitted via Ask IT is 1
Col1 Col2 Col3 Col4
8821015m14:44.0E__uMail = 1
9266321m34:13.0E__uMail = 2
92677165m23:43.0E__uMail = 3
9309415m07:24.0E__uMail = 4
[Code] ....
What I need is a formula that will produce this result.
how to list values from multiple columns in a dropdown list based on lookup value of 1st column as below.
This is how the table looks like.
Product MOLD1 MOLD2 MOLD3
4" AB1 AB2 AB3
6" ZA2 zd4 -
This is how the dropdown list should look like for Product 4"
ab1
ab2
ab3
I have some data with recurring key values and differing values in the second column, I need to produce a unique list of key values with the second values concatenated together.(See below)
The data can be 10 rows to 5000 and I can have anything from 5 to 150 sheets (Separate data sets), a macro would go a long way to keeping me sane.
Sample data Required Output
A | B Z
1| 10 | a 10,a,b,c
2| 10 | b 11,a
3| 10 | c 12,a,b
4| 11 | a
5| 12 | a
6| 12 | b
My system is Windows 8 Excel 2010.
Sheet2 contains various data, including one column of dates.
What I would like to do is populate Sheet1 with functions that search Sheet2 for date values between two separate date values on Sheet1 (cells F1 and G1). When a match is found on Sheet2, row contents from Sheet2 should populate on Sheet1.
In addition, I would like the functions only to match listings in which the value in column E on Sheet2 is greater than zero.
I have attached a sample workbook that illustrates the structure of the worksheets and the desired results. Looking forward to finding a good solution.
1. Using a formula, I am trying to to obtain a list of unique values (string) (caveat: see #2) from the range E2:E10000 (arbitrarily chose 10000 - the row number is variable)(see #3).
I currently have a formula that seems to work for this purpose but I don't know how to add the condition in #2 (below)
2. To include all unique string values except those starting with the letters "IC"
3. Is there a way to make this formula so that it can only seek values up to the last row, and not go to the 10000th row if not necessary? The E column has no empty cells until after the last row that contains data.
Here is the formula I currently use which serves #1 (above):
[Code] .....
Any way to improve/simplfy this formula for the purpose describbed in #1? How can I add the condition in #2? Can you see a way to include #3? The most important issue here is #2.
Example of desired results:
Column A | Column B
AA | AA
DD | CC
AA | DD
CC |
DD |
DD |
IC |
IC |
compare some list of values in order to fiind the common values.
View 1 Replies View Relatedhow I can list 3 different values to a list of list of 3 in a combo box, is.
combo box
select high = 35
select middle 30
select low = 25
I have the names in the box I just need it to add a value (which I have listed 1 in each separate cells) to each selection
I have two lists in the same workbook:
List 1) Contains customer contact information, including an account number. These account numbers may be duplicated in the list.
List 2) Contains account numbers of customers who wish to be removed from the first list.
I need to remove the rows from the customers list (List 2) where the account numbers match, and also copy those to another list for review. So far, I can manually choose and run some code to remove one particular account number only (eg 123):
Set FoundCell = Range("A:A"). Find(What:="123")
'Locate information to remove
Do Until FoundCell Is Nothing
FoundCell.EntireRow.Copy
Sheets(" Deleted List").Select
'ActiveSheet.Next.Select
Range("A1").Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select..................
I have two worksheets, one which contains a list of workstation numbers, and another which contains all of the workstation numbers and additional information on the workstations in the company.
I want to remove all workstation numbers and their information which are not present on the first list from the second list.
In the first w.orksheet each workstation number is in a seperate row, and in column one (seperate cells).
In the second worksheet, each workstation number and its corresponding information is on the same row, and each different workstation is on a seperate row with its information.
I'm looking for a way to get a unique list from a column to a data validation drop down list. Any fancy formula or vba script to create a UDF which. Does this?
View 5 Replies View Related1. I have a list of several items in sheet 1. Lets say 30-40 rows.
2. I do a frequently update of this list.
3. In sheet 2 I want to have a copy of this list in row 1 from column A to Column x (depends on how long the list in sheet 1 is.)
4. When I change the list in sheet 1, could be in first, middle or last row- mayby several rows. The list in sheet 2 should be updated
5. The update of sheet 2 should insert a new column on the right place, according to the list in sheet 1.
I've been searching the forums for this problem but I can't seem to find any answers. Anyway, this is the problem. See screenshot.
I want to compare A1 for the values in column B, then return the corresponding cell (column C) in column D.
e.g. D1 = 2, D2 = 1, D3 = 4, D4 = 5 and D5 = 3.
I am looking for a formula to search two columns.
1) The first column needs to be search to match a text string.
2) If the text string matchs, I need excel to evaluate the number in the second column and find the MIN value of all numbers associated with the text string.
So:
Joe 50
Jim 12
Joe 10
Rob 25
Jim 8
Rob 99
When searching for "Joe", the function would return 10. When searching for "Jim" the function would return 8. For "Rob", it would return "25".