How do I make specific cells in excel Appear in other columns?

Question

Ok, so I have data like this:

Serial        /   Total   /   
123456      /   $10.00/ 
123456      /   $10.00/ 
123456      /   $10.00/ 
Total Amnt /   $40.00/
444444      /   $50.00
444444      /   $50.00
444444      /   $50.00
Total Amnt /  $150.00

I have a list of these that goes on forever. I need to filter out the total amounts, without taking in consideration the other values. So I need to only consider the totals, not the small amouns. Any idea how to make a filter to either consider only the totals ot move the totals into a different columns so that I can use a filter to sort them out or not?

Thank you for your help!!!

garbo7441 · Accepted Answer

Assuming your 'Total Amnt' values are in column A, the following 'toggle' macro will alternately show/hide all rows where column A does not contain 'Total Amnt'.

Copy the macro to the clipboard:

Sub Custom_Filter2()
Dim i, LastRow
LastRow = Range("A" & Rows.Count).End(xlUp).Row
If ActiveSheet.Rows. SpecialCells(xlCellTypeVisible). Count >= 16777216 Then
For i = 1 To LastRow
If ucase(Cells(i, "A").Value) <> "TOTAL AMNT" Then
Cells(i, "A").EntireRow.Hidden = True
End If
Next
Else
ActiveSheet.UsedRange.Rows.Hidden = False
End If
End Sub

Press ALT + F11

INSERT > MODULE

Paste the macro into the module editing area to the right.

Close the VBE and return to Excel.

Press ALT + F8

When the Macros window opens, highlight this macro and click 'Options..'.

Enter a DIFFERENT letter, than the one in your previous question, to be used as a keyboard shortcut and click 'OK'.

Close the Macros window.

To alternately show/hide all rows not containing 'Total Amnt' in column A, press CTRL + your keyboard shortcut letter repeatedly.

IXL@XL · Answer

Row 1 is for headers, cols A and B as shown.
In C2 enter =IF(COUNTIF(A$2:A2,A2)>1,"",A2)
In D2 enter =IF(ISERROR (SMALL(C:C,ROW()-1)), "", SMALL(C:C,ROW()-1))
In E2 enter =IF(D2="","",SUMIF(A:A,D2,B:B))
Copy the 3 formula down to bottom of list. Format col E as currency.

Trending News

How do I make specific cells in excel Appear in other columns?

2 Answers