Recent Questions

I have a list of data that I need to rearrange in a specific way in the next sheet. I want to make the data to automatically be inputted to the next sheet as below as the total data is actually a lot and it takes time to do this by transposing and copy pasting to the correct order. Is it possible to make the data be arranged as such automatically by functions/macros? Let's block ads! بخوانید...

I am trying to split one long row of data into much smaller rows, so I can easily map it to an invoice template that I am plaing to upload into an accounting system. The example provided below is what I am trying to do in google spreadsheets under "Raw Data": Sample Data and what I am trying to do What I am asking is very similar to what is shown on the two links below: How do I split one row into multiple rows with Excel? Splitting one Row with Multiple Columns into Multiple Rows Only problem I have is that I have a lot more columns than theirs to separate into rows I am not used to doing any of this, but I have been trying to dabble in VBA and various excel formulas provided throughout thi...

I am trying to compare data across multiple columns to see if each cell is less than a value in a single cell on the same row. Column B contains the mean across all employees for the variable in Column A (there are 12 variables). Columns C through N contain the mean for each individual employee. For each of the variables, I would like to highlight the cell for employees whose mean is less than the mean of all employees. I can figure out how to do this for each row (compare the values in columns C through N with the value in column B) using conditional formatting using a formula: If Cell Value is...

i have a data frame like ; 1/4/2015 2 CREDT CARD SETTLEMENT 1/4/2015 3 Cash 1/4/2015 4 Cash 1/4/2015 5 Cash 1/4/2015 6 Cash 1/4/2015 7 Cash 1/4/2015 8 Cash 1/4/2015 9 Cash 1/4/2015 10 Cash i wish to replicate the gaps in a way that they are filled by the previous number. more specifically, i want this in the below format. 1/4/2015 2 CREDT CARD SETTLEMENT 222221/4/2015 3 Cash 3331/4/2015 4 Cash 1/4/2015 5 Cash 51/4/2015 6 Cash 1/4/2015 7 Cash 1/4/2015 8 Cash 1/4/2015 9 Cash 101/4/2015 10 Cash i am a fresher in excel. please help. any simple explanation is welcome. This entry passed through the Full-Text RSS service - if this is your content and you're reading it on someone else's site, please...

This is similar to Alteate grid background color in excel when a value of a single column changes? , and I believe it can be done using SUBTOTAL(109,... , but I can't quite figure it out. The first column in my table is a group number, and all rows with the same group number should have the same background. The table is sorted by the group number. I want to alteate the row color per group, based only on the visible rows. In this example I've hidden A and C . Note that praesent and libero have swapped colors based on the visible rows. I'm free to add in hidden helper formula columns, but I'd prefer it all to be in the conditional formatting. This entry passed through the Full-Text RSS service...

This is essentially a variant of the question asked here: Excel- How do I find common text values from multiple (>2) columns? In this case, however, instead of multiple columns of data I need to search multiple rows. So, each row may have up to 17 columns of non-repeated data, and I want to find a match or matches between rows. In the referenced thread, user XOR-LX provided a very useful method for column-wise comparison, but I have been unable to modify it for row-wise use. So far I have attempted to modify it in the following way, with no luck: Range1 = $A$1:$Q$5Arry1 = ROW(Range1)-MIN(ROW(Range1)Arry2 = COLUMN(INDEX(Range1,1,))-MIN(COLUMN(INDEX(Range1,1,)))+1Arry3 = MMULT(0+COUNTIF(OFFSET...

I got an excell sheet with 3 worksheets that I update like every 2-3 days manually by dropping down the date column and it automatically updates the columns based on the date. For example; lets say I opened the excel on april 4th what I do is I drop down the date and until today -1 and following columns gets updated automatically. I basically add in 2 more rows to the worksheet and and columns get updated based on the date. 01.04.2016 12 15 16 19 I would like to make a macro which adds in those needed rows (If i no one updated the excel for like 10 days it needs to add in 9 rows etc) when I hit the button for 3 worksheets at once. Thanks alot. Let's block ads! بخوانید...

I have 300+ rows containing 40+ columns, each column has a number 1-40 at random. I want to know how to search each row for pairs or numbers that occur together within the row. Example. 5 10 12 14 16 17 19 30 31 32 33 40 51 4 7 11 16 17 23 24 26 32 37 39 46 47 1 2 4 7 11 15 18 31 34 35 37 38 43 3 6 14 16 19 21 24 27 33 34 38 42 47 5 7 8 15 17 22 23 24 26 27 30 37 40 I want to know how many time 1 and 2 occur together or 23 and 24, or 5 and 30. How would I do this? which formula would I use and how would I implement it. I've looked at countifs, pivot tables, sumproducts, totals. And I am more confused than when I started. Let's block ads! بخوانید...

Input : MACK 113 445 25 JACK 345 PIKER 30 23 I want the output to be : MACK 113 MACK 445 MACK 25 JACK 345 PIKER 30 PIKER 23 Can anyone please help me out . Thanks in advance....

I'm trying to find a way to easily identify the first ten rows in a table column, no matter how it's been sorted/filtered. Is there a way to use conditional formatting to highlight these cells? Examples of desired results... Sample data: product price units code Item02 15.97 2191 7UQC Item05 12.95 1523 TAAI Item13 9.49 1410 LV9E Item01 5.69 591 6DOY Item04 15.97 554 ZCN2 Item08 10.68 451 2GN0 Item03 13.95 411 FP6A Item07 25.45 174 PEWK Item09 14.99 157 B5S4 Item06 18 152 XJ4G Item10 11.45 148 BY8M Item11 16.99 66 86C2 Item12 24.5 17 X31K Item14 24.95 14 QJEI When sorting by price the first 10 products highlighted differ from those in the next example. table of products sorted by price The fi...

Say I have some Excel columns that look like this: A B C D 1 Amy Bob Carol David 2 Carol David David Carol 3 Bob Carol Amy Amy 4 David Amy David Amy I need to count the number of rows where both Amy and Bob occur, no matter which column they are in. In this example, the number should be 2 (row 1 and row 3). I already know COUNTIFS with which I can count the number of rows with 'Amy' in column A (or any particular column), and 'Bob' in column B (or any particular column). The formula would be: =COUNTIFS(A1:A4,"Amy",B1:B4,"Bob") However, this formula seems to require you to specify the columns. I tried to change it to =COUNTIFS(A1:B4,"Amy",A1:B4,"Bob") and it retued 0, which is not what I'm af...

For example I have the following two rows: X Y Z A B C X Y I want to merge across so the result is centered across the 2 rows like this: X Y Z A B C Please help!...

When adding a filter to an Excel spreadsheet, how do I limit its scope to some rows only? For instance, in this document: the “Hello” column filter will contain the values 0, 2, 4, 5, 7, 9 and 27. How can I make the sum, that is the value 27, disappear from the values suggested by the filter? Let's block ads! بخوانید...

In Row 4 I ask a question and D4 will contain the answer of either Yes or No. If the answer is Yes in D4, I want hidden rows 5 and 5 to appear with follow-up questions. This is working for me: If Range("D4").Value = "No" Then Rows("5:6").EntireRow.Hidden = True ElseIf Range("D4").Value = "Yes" Then Rows("5:6").EntireRow.Hidden = False In Column A I identify the initial questions (like in row 4) as "Tiered Questions" and the subsequent questions (like rows 5 and 6) are labeled "Follow-up Q". Is there a way to do this without manually updating every row? Also, I need to allow for adding/removing rows above which would impact which rows will hide/unhide. Thanks. Let's block ads! بخوانید...

In Excel 2010, one of my "awesome" co-workers set many, many rows to 0.00 pixel height. I need to set ALL rows to any other height but 0. I have tried UNHIDE, tried FORMAT>ROW HEIGHT, tried FORMAT>AUTO FIT ROW HEIGHT, tried COPY>PASTE SPECIAL>VALUES ONLY (and all other PASTE options), tried Selecting the entire sheet then manually resizing with the cursor, but this only works for one row at a time. I have hundreds of these rows to fix, so that's no good. I know my way around Excel very well, but I'm at my wits end. Any help here is appreciated. Tim. Let's block ads! بخوانید...

I want to conditionally copy Excel worksheet rows based on a specific field content of the row to be copied to a different worksheet. The source worksheet is large and some of the fields of the row to be copied have large text data content....

I have 19000 data in each row, now my problem is how to delete 20 rows simultaneously after an interval of 10 rows?.......please help me someone i'm stuck by this shit task, otherwise it will take me more than an hour to complete one task. Thanking you in advance.... This entry passed through the Full-Text RSS service - if this is your content and you're reading it on someone else's site, please read the FAQ at fivefilters.org/content-only/faq.php#publishers....

Not sure if excel has the power to do what I'm asking but I would like to create a worksheet and then from that worksheet choose specific rows to populate another work sheet simply by clicking on the row I want. So for example i have information on rows 2-30 on worksheet A, but on worksheet B I only want rows 2,3,15 and 30. Is there a way I can use 1 cell in worksheet A to populate worksheet B? I know this is asking a lot from excel but if I can get it to work it would save me a lot of time and more importantly cut down a lot on the risk of making a mistake. Any help is appreciated! This entry passed through the Full-Text RSS service - if this is your content and you're reading it on someone...