Data Sorting - Formula Based On Multiple Other Cells

Jul 1, 2014

I have some cells that are summed with total cost.... now i need to sort the totaled cells? How do i sort a cell that has a formula based on multiple other cells?

View 2 Replies


ADVERTISEMENT

Sorting (or Maybe Filtering) Worksheet With Multiple Data In Cells

Nov 1, 2008

Background: I am HR manager for a construction company & keeper of the call-in list of personnel who are looking for work. I have a simple sheet that has columns:

Date Name Craft Experience ...more info...

If each call-in had only one craft, wouldn't have a problem. Those who are multicrafted ar listed e.g. "EL, MW, BM" In the column C. A caller two days later may be listed as "MW, BM, EL" We input the data as they say it since that is usually their order of expertise. (Yes, I know that it should have been set up with each craft having its own column, but I inherited the sheet & it has 4000+ entries)

I wrote a couple of small macros & assigned buttons on the sheet to allow the users to sort the sheet by date, or name, or craft. My customers (project managers) have requested to be able to sort by craft but have all the folks with any specific craft listed together.

Example (Excel 2003): ..

View 4 Replies View Related

Formula To Lookup And Sum Cells Based On Multiple Criteria In Row And Columns?

Jun 17, 2014

I would like to create a formula in a summary sheet ("sheet 2 section" in attached)that looks up and sum cells based on multiple criteria in row and columns in "sheet 1 section". I thought I sumifs would work, but I kept getting #value errors. I'm not a power user in excel. I attached the spreadsheet - it is only an example of what I want to do as the real data is confidential and large. The result I should I get is in section 2. Lookup account 12.251 for tim in the month of February - result is 14.

Test2014.xlsx

View 3 Replies View Related

Sorting Based On Matching Cells

Jun 29, 2006

I have two columns with the same data just totally different orders the third column (associated with the second) has data that I want to sort. I want to keep the order of the first, rearange the second so they match, and have the 3rd column follow the second to the proper location. i need to keep the order of column 1 so i can post into a massive spreadsheet. Theres gotta be a quick formula for this i just have no clue

View 2 Replies View Related

Sorting One Giant Column Into Multiple Columns Based On Cell Color?

Sep 3, 2013

I was handed a pdf of some leads by a co-worker. Finally, I got the data into excel but it's in a pretty useless format... 30,000 rows of contact info with no rhyme or reason. I want to upload this into our CRM, but I need to get all the different bits of info into separate columns. I'm no excel pro, but I managed to use an Excel plugin called ASAP tools to bring some order to this chaos... All names have a blue cell color, all titles have a green cell color, work phone is red, and so on.

Does anyone know a way that I can sort these into separate columns? I've trying playing with the Filter function, but it hides rows which makes the output useless for my purposes.

View 1 Replies View Related

Combing Data From Multiple Cells Based On Value

Jun 18, 2009

In column A i have values that may repeat throughout Column A, then next to the value in ColB, there is a sequence number 1,2, 3 for each of the same value in ColA. Then the Text in ColC that i need.

What i Need is to be able to search ColA, Then get the correct Sequence number from ColB, and place the contents in ColC into ColD or into a new worksheet if necessary.

View 10 Replies View Related

All Information In One Cell Instead Of Multiple Cells - Sorting Dataset

Oct 17, 2013

I downloaded a massive dataset in .csv format to work with. My first problem is that I'm having trouble sorting the dataset. When I open the .csv-file every bit of information comes up in the A-column instead of having INFORMATION1 in the A-column INFORMATION2 in the B-column and so on.

View 1 Replies View Related

Sorting Data That Has Multiple Formats?

Jun 15, 2012

I have lists of products that I want to sort, but all the skus are in different formats, some letter, numbers, spaces, symbols and mixes of all.

Example:
01107919
CE-4128B9
RED-BRIC889
1124
14100119020-E
86TR
ECO CUP22OZ/1000
1418909888783301

I would like to organize them by the order of the numbers, letters and symbols, not the value of the numbers.

Example:
01107919
1124
14100119020-E
1418909888783301
86TR
CE-4128B9
ECO CUP22OZ/1000
RED-BRIC889

I have tried formatting and different sorting options but there's a dent in the wall and my forehead is getting bruised from all of the banging.

View 2 Replies View Related

Sorting Multiple Sets Of Data

Aug 22, 2012

Create a macro or some other solution to sorting my data. Just clicking on Sort Ascending isn't good enough because it'll sort everything and remove the empty rows. However, I want the empty rows to stay there. The way that I've been doing this is manually. Basically, after the empty rows were inserted, I'd click on a cell in Column A and click on Sort Ascending, then I'd scroll down and click on the next set of data and click on Sort Ascending, then the next set and the next. I'd end up having to do this hundreds of times.

I've uploaded an example of what I'm talking about, I also labeled the tabs as "I want this" and "to look like this":

Example file.xls

View 5 Replies View Related

Sorting Multiple Lines Of Data

Nov 20, 2007

I have a large spreadsheet I need to sort into alphabetical manager order.

As there are between 2 and 20 rows per manager I would like to know if I am able to sort this into alphabetical order!

View 9 Replies View Related

Formula To Pull Data Based On One Cells Value

Apr 23, 2014

I am trying to have my spreadsheet pull data from one tab and cell if they match another.

Tab "Ag Orders" column H, I enter data in lets say cell H37. Whatever I enter, if it matches tab "reference List" C:C, then "ag orders" column G (specifically G37) should enter the data in "reference List" D:D

So since in H37 I entered 34000 - 314620, G37 should pull in Farm Operations since on the referene list tab that string of numbers (C11 matches up with D11)

a little confusing but I hope it makes sense.

13-14 budget-AG.xlsm

This isn't the exact formula I used but something like it and it wasn't working. It wouldn't match the data up correctly on a few of the department names

=IFERROR(LOOKUP(H35,("11000 - 311010","11000 - 311020","11000 - 311510","11000 - 311610","11000 - 312010","17800 - 311010","17800 - 311510","17800 - 312010","34000 - 314610","34000 - 314620","34000 - 314640","34000 - 314660","34000 - 314680","34000 - 314690","81008 - 840008","81009 - 840009","13863 - 312040","13864 - 312050","71060 - 521620"),

[Code] ..........

View 1 Replies View Related

Can A Formula Change Based On Data In Other Cells

Feb 25, 2004

What I mean is, I have a formula that sums a specific cell across several tabs.

=sum('120:125'!C15)

Lets say that this formula resides in a summary sheet. And that the summary sheet is definable by a control in another sheet (user friendly).
So in the user friendly sheet, a user toggles to 'Assets'. In the Summary sheet, Assets appears in cell a1, there is a lookup table somewhere, and a lookup value of 120 appears in cell a2.

I want the formula above to adjust for cell a2.
If a2 = 115, then the formula should adjust to

=sum('115:125'!C15)

Of course after I understand how to do this, I can do the same with the end of the sheet range and the cell ref.

View 9 Replies View Related

Sorting Data Based In Word

Jan 21, 2008

if there is anyway of sorting data from a word document into an excel spreadheet?

I want to sort be able to export the data according to the column on the far left hand side (month), into excel.

ideally i would have liked to have all the data in excel as it would make things easier but in this instance i need to keep all the data input in word.

View 9 Replies View Related

Sorting Data In Multiple Tabs By Group

Dec 31, 2012

I have imported data into excel arranged as per the following and there are separate tabs per year. I a trying to summarize and phase the balances per client..i.e 07,08,09,10,11,12.

Therefore if client x balance in 2007 = 10, 2008=11, 2009=10, 2010=2, 2011=3 and 2012 = 5 and so on...then total the balance owed as of now and also phase it as illustrated below. I've tried vlook up's?

Client
Code
01Jan07-
31Jan07
01Feb07-
28Feb07

[Code] .....

View 1 Replies View Related

Sorting Data With Formula?

Jul 5, 2014

I have a list in a column A which has data which is not in alphabetical order and can have duplicates. In need to sort this data in next column B. I need a formula for this so that the new data is dynamic. I have checked the following thread which gives me formula

{=INDEX($A$2:$A$9,MATCH(0,COUNTIF($A$2:$A$9,"

View 4 Replies View Related

Formula That Resizes Text Box Based On Data In Two Cells?

Jan 17, 2013

What type of formula would resize a text box based on the input of two cells? Here's what I'm trying to accomplish:

A1 - width (in inches)
A2 - height (in inches)

Converts the text box to that height & width.

This is for a sign request form I am creating that would give our requestors the ability to see the size layout of their sign and then input their text to match what they're looking for.

View 3 Replies View Related

Sorting And Retrieving Data Based On Two Columns?

Oct 19, 2012

I am working on a new budget spreadsheet that I am creating. I want to accurately calculate credit card expenses. As everyone who has a credit card knows, charges made this month are not due to be paid until next month. To make matters more complex, charges made from (for example) January 20 until February 19 are not due to be paid until March 20. With that in mind, I created a spreadsheet with a different tab for each month. Each tab has a place to put in all expenses. Here is an example:

date
Category
Expenses
Remarks

[Code].....

Obviously, The above example is from January. IN the February tab, I want to calculate everything in the "Expense" column that has a date in the "Date" Column before Jan 20 and where it says "Kohls - Frank" in the Category column. Of course, in the March tab, I would need to grab all of the "Kohls - Frank" expenses after Jan 20 from the January tab, and everything before Feb 20 from the February tab.

I figured out how to add up everything with the following formula: =SUMIF(Jan!A:A,("<="&DATE(2013,1,20)&E32),Jan!C:C) How can I filter on the Category tab?

View 1 Replies View Related

Macro That Will Auto Color Multiple Cells Based On Data Displayed In One Cell

Jan 7, 2013

I am trying to find a way to write a Macro that will auto color multiple cells based on what data is displayed in one cell. The cell I want to reference is a vlookup cell.

Basically this is a part label. And depending what part is selected from the list my vlookup will display its position on a vehicle(i.e.. FR, FL, RR, RL, Etc..). So if vlookup comes back with FR I want the various cells on the label to be orange, etc..

ALSO: if there is a way to embed it so it does this automatically (rather than run the macro each time).

View 7 Replies View Related

Excel 2013 :: Error When Sorting Data With INDEX Formula?

Mar 18, 2014

I've got large set of data(Column E) for dates from 2007 to 2013(Column B). Here dates are in chronological order.(Attachement) Now I want to match the data in to dates in Column H. Here dates are in random order. I used =INDEX(E3:E40582; MATCH(H3;B3:B40582; 0)). But it doesn't work.

View 5 Replies View Related

Sorting Data Doesnt Move Hyperlinked Cells

Sep 20, 2007

Ive spent a few hours adding email hyperlinks to data in column B.

I now want to sort the data in the worksheet into ascending order using the data in column A.

The problem is, none of the hyperlinks from column B sort with the data in column A, they are still in the cell locations I had originally entered them.

View 9 Replies View Related

List Displaying Data In Multiple Cells Based On List Selection

Jun 24, 2009

I am trying to create a drop down list that when selected will display data in the 10 rows below and 4 columns, so a total of 40 cells. I would like the data to be based on the selection in the drop down list.

The data looks like this,
RequirementsQtyResourcesQty
Level 1 Sentinel10Level 1 Sentinel105
Level 2 Sentinel10Level 2 Sentinel167
Level 3 Sentinel10Level 3 Sentinel156
Level 1 Banga20Level 1 Banga401
Level 2 Banga20Level 2 Banga307
Level 4 Banga20Level 4 Banga0

i have 27 different combinations of the above list with up to 10 different rows. I have a label for each one, but i cannot get the rows below my drop down list to display the proper values base don the selection in the drop down list.

View 8 Replies View Related

Populating Fields In Multiple Cells Based On Criteria In Multiple Columns?

Feb 24, 2014

Im struggling to find a formula to populate cells based on values to be checked in two fields (ICODE, COMPANY) as shown below.

In the example here, all records where ever the ICODE = 49, i need to populate the fields(columns) AD1,AD2,AD3 or AD4 with the product name of the same company where the ICODE <> 49. There could be between 1-7 products per company.

CURRENT SHEET

ICODE PRODUCT COMPANY AD1 AD2 AD3 AD4
89PENS ABC STATIONERY CO LLC [code]....

The attached example file may be a better alternative to view this problem.

View 2 Replies View Related

Return Multiple Results In Multiple Cells Based On Criteria In Yet Another Cell?

Feb 1, 2012

Worksheet 1: In column A I have a people's initials. In coumn B I have text boxes with miscellaneous text. (The same person could have multiple rows within this sheet.)

e.g.
AAA blue
BBB orange
AAA round
CCC smelly
AAA elongated

Worksheet 2 I want to show:
A2 = initials, B2 = first text box associated with that person, C2 = second text box (different row) associated with that person (if applicable), D2 = third text box (different row) associated with that person (if applicable), etc.

e.g.
AAA blue round elongated
BBB orange
CCC smelly

View 3 Replies View Related

Select Multiple Cells Based On Multiple Criteria?

Sep 25, 2013

I would like a macro that select all cells based on multiple criteria. It's a big sheet, but I've made it smaller in the following example.

In row 1, there are 2 headers, in B1 (electricity) and C1 (gas) In column A, there are 3 houses, in A2, A6 and A10. The blocks of cells in between (e.g. B2:B5) have 2 headers, 'period' in B2, and 'account number' in B4. B3 and B5 have a list of options in them.

I want to select cells that meet certain criteria. Either electricity or gas, and either period or account number. So, if in cell D1 I choose electricity and in D2 period, I would like to select B3, B7 and B11. If I choose gas and account number, I wish to select C5, C9 and C13.

View 1 Replies View Related

Color Cells Based On Multiple Conditions Of Different Cells

Jan 15, 2008

Im trying to change color in cell B9 according to different conditions in Cell C9 (than on b10 according to c10 and so long until necessary - probably will be around 2000 lines). Since there are more than 3 conditions and in two cases condition depends on the color of the C Column, I can not use conditional formatting. Ive searched and found similar forums here, but since Im ignorant in VBA code, I couldnt manage to make adjustments. So if you can help me with the code, I would really appreciate it!

Condition and Results required would be:

IF column C = S than on Column B = color cell light blue with white border
IF column C = P than on Column B = color cell Green with white border
IF column C = A than on Column B = color cell Yellow with white border
IF column C = L than on Column B = color cell Red with white border
IF column C = C than on Column B = color cell Dark Blue with white border
IF column C = than on Column B = color cell white
IF column C = V and has white background than on Column B = color cell white
IF column C = V and has a Dark Grey background than on Column B = color cell Dark Grey

I dont know if the last two are feasible.

View 6 Replies View Related

Summing Data From Multiple Workbooks Based On Multiple Criteria

May 14, 2014

I track distribution using 1 workbook for each sales rep. Each workbook has a separate worksheet (scorecard) for each of their locations showing which of our products are used in that location, for various functions. Each of the locations has a segment (bar, nightclub, casual dining, etc.), and a class (AA, A, B, etc.). All of the scorecards are identical, and are in the same file folder on our shared drive.

Sample:

Location A Class:AA Segment: Sports Bar Rep: John Smith

Product: Product X Product Y Product Z
Well: 1 0 0
Back Bar: 0 1 0
Cocktail Menu: 0 1 1

I was unable to find an HTML maker so this is not an actual sample but what I'm looking at. The above data indicates that for location A (Sports Bar, Class AA), Product X is used as the well, Product Y is on their back bar and their cocktail menu, and Product Z is not used at this location at all.

I need to set up an analytic workbook where I can sum data based on multiple and grouped criteria, and for multiple reps ie- display a scorecard that shows totals for nightclubs and sports bars, class AA, A, and B, for a particular rep; OR display a scorecard that shows totals for nightclubs, class A for a group of reps. Ideally I would like to do this using drop down menus that allow the user to select multiple criteria in each of the drop downs, having the data auto-populate based on those selections.

View 2 Replies View Related

Pull Data From Multiple Cells And Concatenate In Single Cell Using Multiple Criteria

Aug 31, 2012

I have a worksheet entitled 'Data'. In this worksheet there is a table consisting of 4 columns plus relevant data:

TABLE 1:

Project
Benefit Type
Delivered or Enabled
Benefit

PJ1
Financial
Delivered
Saving of $4M over 24 months.

[code]....

I have been trying to create a formula that will enable me to pull data from the 'benefit' column(column D) so that the cell contents populate in a single cell in a table in a different worksheet.

TABLE 2:

Financial - Delivered
Financial - Enabled
Tech - Delivered
Tech - Enabled
Green - Delivered
Green - Enabled

[code]....

So, as an example, I am hoping that a formula can be created which pulls the text from relevant cells in column D when criteria from columns A, B and C are met e.g. Tech benefits that are Delivered in PJ2 would populate cell E3 ('Tech -Enabled') in Table 2 with:

Continued maintenance of hardware.

Increased capacity.

View 1 Replies View Related

SUMIF - Summing Data From Several Specified Cells Based On Data Of Corresponding Cells?

Aug 6, 2014

On the "CurrentCustomer" worksheet tab I am trying to accomplish the following:

When the merged cells in the F column (F3, F25, F47...F443) equal the word "Contract", I need the corresponding total cells in the E column (E23, E45, E67...E463) to add up, if and only if the corresponding F column merged cell equals "Contract".

For example, using the first three ranges, if F3, F25, & F47 all equal "Contract", I need the corresponding E23, E45, & E67 to add up. If F3 & F47 equal "Contract", but F25 equals anything other than contract I need E23 & E67 to add up only.

View 3 Replies View Related

Convert Formula To Value Based On Multiple Condition

Mar 17, 2013

I have workbook with multiple sheets with full of formulas. I need a excel macro which can convert formulas to values based on multiple conditions.

The formulas are in B2 and below.This formula provides results as soon as a value is entered in C2 and one among D2 and E2 cells (see attached excel)I want a excel macro to convert the values provided by formula to actual values as soon as results are obtained.Further, when user delete data in C2, D2 and E2, i want the formula to be working again since if a user enters different data in C2, D2, or E2 the value should get updated.

I found a similar post which addresses some part of my problem but works only with one condition. [URL] ....

View 8 Replies View Related

Formula To Count Based On Multiple Criteria

Dec 7, 2013

What I require : Column F to be blank as long as the following is true:

1) Column A (Order) cannot be blank.
2) Column E (Status) has to be equal to either "Completed" or "Successful".
3) Column D (Sales) has to have the same date as Column C (Contact), but due to bad data quality each order number can have several rows and as long as the Sale date from one of the other "Duplicate" rows equals the contact date it needs to be counted. This is as long as there is not already a row for the Order that already has Sales / Contact day matching.

In the attached spreadsheet, I have highlighted the rows that should be blank in column F in green. My attempts at formulas to count this are in the columns highlighted in red.

WORKAROUND.xls‎

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved