Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to filter two columns
I have two columns, for simpicity I'll name them column A and Column B.
To illustrate my data on sheet1: Column A header column B header Record 1 <blank <blank 05/20/06 Record 1 related info-A <blank 06/29/06 Record 1 related info-B Record 2 <blank <blank 06/29/06 Record 2 related info-A <blank 03/01/07 Record 2 related info-B <blank 04/05/07 Record 2 related info-C <blank 06/22/08 Record 2 related info-D Record 3 <blank <blank 01/29/08 Record 3 related info-A <blank 07/29/08 Record 3 related info-B <blank 02/27/09 Record 3 related info-C Note, <blank means empty cell So basically I need a macro to get the latest entry info from column B and moved to the <blank cell adjacent to column A for each item existing in column A and delete all other blank rows in column A. So after the macro, it should look like: Column A column B Record 1 06/29/06 Record 1 related info-B Record 2 06/22/08 Record 2 related info-D Record 3 02/27/09 Record 3 related info-C Note, all items in column A and column B are dynamic and cannot specify a fixed range. Any help on hard coding to get me started would be appreciated. Thank you. *** Sent via Developersdex http://www.developersdex.com *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to filter two columns
Polo78,
Try the macro below. HTH, Bernie MS Excel MVP Sub Macro1() Dim i As Integer With Columns("B:B").SpecialCells(xlCellTypeBlanks) For i = 1 To .Areas.Count - 1 .Areas(i)(1).Formula = "=" & .Areas(i + 1)(0).Address Next i ..Areas(.Areas.Count)(1).Formula = _ "=" & IIf(.Areas(.Areas.Count)(3) = "", _ .Areas(.Areas.Count)(2).Address, _ .Areas(.Areas.Count)(2).End(xlDown).Address) End With Columns("B:B").Copy Columns("B:B").PasteSpecial xlPasteValues Columns("A:A").SpecialCells(xlCellTypeBlanks).Enti reRow.Delete End Sub "Polo78 Lacoste" wrote in message ... I have two columns, for simpicity I'll name them column A and Column B. To illustrate my data on sheet1: Column A header column B header Record 1 <blank <blank 05/20/06 Record 1 related info-A <blank 06/29/06 Record 1 related info-B Record 2 <blank <blank 06/29/06 Record 2 related info-A <blank 03/01/07 Record 2 related info-B <blank 04/05/07 Record 2 related info-C <blank 06/22/08 Record 2 related info-D Record 3 <blank <blank 01/29/08 Record 3 related info-A <blank 07/29/08 Record 3 related info-B <blank 02/27/09 Record 3 related info-C Note, <blank means empty cell So basically I need a macro to get the latest entry info from column B and moved to the <blank cell adjacent to column A for each item existing in column A and delete all other blank rows in column A. So after the macro, it should look like: Column A column B Record 1 06/29/06 Record 1 related info-B Record 2 06/22/08 Record 2 related info-D Record 3 02/27/09 Record 3 related info-C Note, all items in column A and column B are dynamic and cannot specify a fixed range. Any help on hard coding to get me started would be appreciated. Thank you. *** Sent via Developersdex http://www.developersdex.com *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to filter two columns
Thank you so much!! Coding was flawleses.
Thank you in advance. Newbie to VBA. *** Sent via Developersdex http://www.developersdex.com *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to filter two columns
I have another dilemma, hope you help me out on this one. Original data
has 4 columns, driven by column A. This is somewhat a transpose but on multiple columns. Before macro run Column A column B Column C Column D Column E Client 1 item qty date1 date2 <blank item2 qty date1 date2 <blank item3 qty date1 date2 Client 2 item qty date1 date2 <blank item2 qty date1 date2 Client 3 item qty date1 date2 <blank item2 qty date1 date2 <blank item3 qty date1 date2 <blank item4 qty date1 date2 After macro run Column A Column F Client 1 item-qty-date; item2-qty-date2; item3-qty-date2 Client 2 item-qty-date; item2-qty-date2; Client 3 item-qty-date; item2-qty-date2; item3-qty-date2; item4-qty-date2 After the macro is finished, any blank data in column A should be deleted. Also Column F should be appended to Column A-E (totalling 6 columns when report is done). Need VBA macro coding to help out and get started. Thank you in advance. Newbie to VBA. *** Sent via Developersdex http://www.developersdex.com *** |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to filter two columns
Hi
Try this: Sub bbb() FirstRow = 2 'Headings in row 1 LastRow = Range("B" & Rows.Count).End(xlUp).Row For r = FirstRow To LastRow If Cells(r, 1) = "" Then LastCol = Range("A" & TargetRow).End(xlToRight).Column Cells(r, 2).Resize(1, 4).Cut Destination:=Cells(TargetRow, LastCol + 1) Else TargetRow = r End If Next Range("A1", Cells(TargetRow, 1)).SpecialCells (xlCellTypeBlanks).EntireRow.Delete End Sub Regards, Per On 7 Maj, 03:05, Polo78 Lacoste wrote: I have another dilemma, hope you help me out on this one. Original data has 4 columns, driven by column A. This is somewhat a transpose but on multiple columns. Before macro run Column A * *column B Column C Column D *Column E Client 1 * *item * * qty * * *date1 * * date2 <blank * * item2 * *qty * * *date1 * * * date2 <blank * * item3 * *qty * * *date1 * * * date2 Client 2 * *item * * qty * * *date1 * * date2 <blank * * item2 * *qty * * *date1 * * * date2 * Client 3 * *item * * qty * * *date1 * * date2 * <blank * * item2 * *qty * * *date1 * * * date2 <blank * * item3 * *qty * * *date1 * * * date2 <blank * * item4 * *qty * * *date1 * * * date2 After macro run Column A * Column F Client 1 * item-qty-date; item2-qty-date2; item3-qty-date2 Client 2 * item-qty-date; item2-qty-date2; * Client 3 * item-qty-date; item2-qty-date2; item3-qty-date2; item4-qty-date2 After the macro is finished, any blank data in column A should be deleted. Also Column F should be appended to Column A-E (totalling 6 columns when report is done). Need VBA macro coding to help out and get started. Thank you in advance. Newbie to VBA. *** Sent via Developersdexhttp://www.developersdex.com*** |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to filter two columns
I forgot to mention that all the columns on F and after, should all be
in one cell, so when there is a vlookup on a column A, column F has all the data. Im trying to figure out how to concat the cells data. You did an excellent job by the way. Thank you in advance. Newbie to VBA. *** Sent via Developersdex http://www.developersdex.com *** |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to filter two columns
Try this version:
Sub bbb2() FirstRow = 2 'Headings in row 1 LastRow = Range("B" & Rows.Count).End(xlUp).Row For r = FirstRow To LastRow If Cells(r, 1) = "" Then LastCol = Range("A" & TargetRow).End(xlToRight).Column myVal = "" myVal = Cells(r, 2).Text For i = 1 To 3 myVal = myVal & "-" & Cells(r, 2 + i).Text Next i Cells(TargetRow, LastCol + 1).Value = myVal Else TargetRow = r End If Next Range("A1", Cells(TargetRow, 1)).SpecialCells(xlCellTypeBlanks).EntireRow.Delet e End Sub HTH, Bernie MS Excel MVP "Polo78 Lacoste" wrote in message ... I forgot to mention that all the columns on F and after, should all be in one cell, so when there is a vlookup on a column A, column F has all the data. Im trying to figure out how to concat the cells data. You did an excellent job by the way. Thank you in advance. Newbie to VBA. *** Sent via Developersdex http://www.developersdex.com *** |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to filter two columns
Bernie,
Im trying to decifer the coding that you did, so I can try to understand your coding and learn VBA more.. Basically, Im still having a problem with the if statement that you place If Cells(r, 1) = "" , the problem is, if the client has only one record, it skips the row and does not place the contents on column B, C, D, on the column F. Also, I noticed the items are still not being placed on column F. I cannot have more than 6 columns, total, regardless of how many items there are for each client. Pls advise what is the vba function/formula to concat and hold myVal to contain all previous items, so when the data is ready to be copied, it will place all the data on column F. Thank you again. BTW, your coding is awesome! Thank you in advance. Newbie to VBA. *** Sent via Developersdex http://www.developersdex.com *** |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to filter two columns
Sub bbb3()
FirstRow = 2 'Headings in row 1 LastRow = Range("B" & Rows.Count).End(xlUp).Row For r = FirstRow To LastRow If Cells(r, 1) < "" Then TargetRow = r addon = False Else addon = True End If LastCol = 6 myVal = "" myVal = Cells(r, 2).Text For i = 1 To 3 myVal = myVal & "-" & Cells(r, 2 + i).Text Next i Cells(TargetRow, LastCol).Value = _ Cells(TargetRow, LastCol).Value & IIf(addon, "; ", "") & myVal Next r Range("A1", Cells(TargetRow, 1)) _ .SpecialCells(xlCellTypeBlanks).EntireRow.Delete End Sub HTH, Bernie MS Excel MVP "Polo78 Lacoste" wrote in message ... Bernie, Im trying to decifer the coding that you did, so I can try to understand your coding and learn VBA more.. Basically, Im still having a problem with the if statement that you place If Cells(r, 1) = "" , the problem is, if the client has only one record, it skips the row and does not place the contents on column B, C, D, on the column F. Also, I noticed the items are still not being placed on column F. I cannot have more than 6 columns, total, regardless of how many items there are for each client. Pls advise what is the vba function/formula to concat and hold myVal to contain all previous items, so when the data is ready to be copied, it will place all the data on column F. Thank you again. BTW, your coding is awesome! Thank you in advance. Newbie to VBA. *** Sent via Developersdex http://www.developersdex.com *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel auto-filter does not filter certain columns | Excel Discussion (Misc queries) | |||
Macro to filter 3 columns and then copy data then do the same again........ | Excel Programming | |||
Need macro to filter, create tab on filter and copy/paste | Excel Programming | |||
Need macro to filter, create tab on filter and copy/paste | Excel Programming | |||
Excel button :: Filter columns by value - possible? Additionally, hide certain columns | Excel Programming |