| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup function, but then a bit complicater
I am having the following set of data. 14000 rows.
A B 1010071 80425 1010071 80253 1010071 80425 1010071 80253 1010083 80451 1010083 80205 1010083 81853 1010083 80451 1010083 80205 1010083 81853 and so on... What I want to is this: Make a colum for C: Looking like this: 1010071 1010083 and so on.. = peace of cake and is done. But now is the problem. Behind this colum I want some colums holding the data for B. Looking like this 1010071 80425 80253 1010083 80451 80205 81853 80451 80205 81853 and so on.... I am having absolutely NO IDEA how to fix this one. any help would be welcome since doing this by hand is too much..I am pretty familiar with excel ( and a little with VBA) Thanks for any advice. Robert |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Lookup function, but then a bit complicater
Public Sub ProcessData()
Const TEST_COLUMN As String = "A" Dim i As Long Dim LastRow As Long With ActiveSheet LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row For i = LastRow To 2 Step -1 If .Cells(i, TEST_COLUMN).Value = _ .Cells(i - 1, TEST_COLUMN).Value Then .Cells(i, "B").Resize(, 250).Copy .Cells(i - 1, "C") .Rows(i).Delete End If Next i End With End Sub -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Robert" wrote in message ... I am having the following set of data. 14000 rows. A B 1010071 80425 1010071 80253 1010071 80425 1010071 80253 1010083 80451 1010083 80205 1010083 81853 1010083 80451 1010083 80205 1010083 81853 and so on... What I want to is this: Make a colum for C: Looking like this: 1010071 1010083 and so on.. = peace of cake and is done. But now is the problem. Behind this colum I want some colums holding the data for B. Looking like this 1010071 80425 80253 1010083 80451 80205 81853 80451 80205 81853 and so on.... I am having absolutely NO IDEA how to fix this one. any help would be welcome since doing this by hand is too much..I am pretty familiar with excel ( and a little with VBA) Thanks for any advice. Robert |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Lookup function, but then a bit complicater
On Dec 4, 2:58 pm, Robert wrote:
I am having the following set of data. 14000 rows. A B 1010071 80425 1010071 80253 1010071 80425 1010071 80253 1010083 80451 1010083 80205 1010083 81853 1010083 80451 1010083 80205 1010083 81853 and so on... What I want to is this: Make a colum for C: Looking like this: 1010071 1010083 and so on.. = peace of cake and is done. But now is the problem. Behind this colum I want some colums holding the data for B. Looking like this 1010071 80425 80253 1010083 80451 80205 81853 80451 80205 81853 and so on.... I am having absolutely NO IDEA how to fix this one. any help would be welcome since doing this by hand is too much..I am pretty familiar with excel ( and a little with VBA) Thanks for any advice. Robert Hi 1.Do text to columns, using the space to seperate them. Soppose the data is now in columns A and B and starts in row 1 2. Type this formula in C1 =IF($A16=$A15,B16,"") 3. Drag the formula down column C 4. If there are any none blanks in column C, drag the formula to column D. Repeat until you have a column of blanks 5. Copy all your data and paste special...values to where it is, so that you remove the formulas. regards Paul |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Lookup function, but then a bit complicater
On Dec 4, 3:29 pm, wrote:
On Dec 4, 2:58 pm, Robert wrote: I am having the following set of data. 14000 rows. A B 1010071 80425 1010071 80253 1010071 80425 1010071 80253 1010083 80451 1010083 80205 1010083 81853 1010083 80451 1010083 80205 1010083 81853 and so on... What I want to is this: Make a colum for C: Looking like this: 1010071 1010083 and so on.. = peace of cake and is done. But now is the problem. Behind this colum I want some colums holding the data for B. Looking like this 1010071 80425 80253 1010083 80451 80205 81853 80451 80205 81853 and so on.... I am having absolutely NO IDEA how to fix this one. any help would be welcome since doing this by hand is too much..I am pretty familiar with excel ( and a little with VBA) Thanks for any advice. Robert Hi 1.Do text to columns, using the space to seperate them. Soppose the data is now in columns A and B and starts in row 1 2. Type this formula in C1 =IF($A16=$A15,B16,"") 3. Drag the formula down column C 4. If there are any none blanks in column C, drag the formula to column D. Repeat until you have a column of blanks 5. Copy all your data and paste special...values to where it is, so that you remove the formulas. regards Paul- Hide quoted text - - Show quoted text - Oops Forgot to add the filtering bits: 0. Highlight the two columns, and do an advanced filter...choose "copy to another location" and select unique items. Choose the Copy To cell on a new sheet in A1 .... and 6. Highlight column A and do another advanced filter...choose "Filter the list in place" and select unique items 7. Copy the filtered data to a new location and you are done. regards Paul |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Lookup function, but then a bit complicater
Thank you for the advice. I managed to create my routings. The filter
didnt do what it is supposed to do. Maybe beaceause of some inconsistent data. I second row, I replaced the codes with letters, so I could easily concatenate them. 1010479 L LKRM 1010479 K KRM 1010479 R RM 1010479 M M 1010490 H HSN HSN HSN 1010490 S SN SN 1010490 N N N What I now want is only the above in 2 new colums. 1010479 LKRM 1010490 HSN Is there an easy way to do is also? On 4 dec, 16:42, wrote: On Dec 4, 3:29 pm, wrote: On Dec 4, 2:58 pm, Robert wrote: I am having the following set of data. 14000 rows. A B 1010071 80425 1010071 80253 1010071 80425 1010071 80253 1010083 80451 1010083 80205 1010083 81853 1010083 80451 1010083 80205 1010083 81853 and so on... What I want to is this: Make a colum for C: Looking like this: 1010071 1010083 and so on.. = peace of cake and is done. But now is the problem. Behind this colum I want some colums holding the data for B. Looking like this 1010071 80425 80253 1010083 80451 80205 81853 80451 80205 81853 and so on.... I am having absolutely NO IDEA how to fix this one. any help would be welcome since doing this by hand is too much..I am pretty familiar with excel ( and a little with VBA) Thanks for any advice. Robert Hi 1.Do text to columns, using the space to seperate them. Soppose the data is now in columns A and B and starts in row 1 2. Type this formula in C1 =IF($A16=$A15,B16,"") 3. Drag the formula down column C 4. If there are any none blanks in column C, drag the formula to column D. Repeat until you have a column of blanks 5. Copy all your data and paste special...values to where it is, so that you remove the formulas. regards Paul- Hide quoted text - - Show quoted text - Oops Forgot to add the filtering bits: 0. Highlight the two columns, and do an advanced filter...choose "copy to another location" and select unique items. Choose the Copy To cell on a new sheet in A1 ... and 6. Highlight column A and do another advanced filter...choose "Filter the list in place" and select unique items 7. Copy the filtered data to a new location and you are done. regards Paul |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Lookup function, but then a bit complicater
Hi
Put column headings on your data. Select the first column only Do Filter, Advanced Filter, Filter in place and tick "Unique Items Only". Copy what you get to a new location and delete the columns you don't want. regards Paul On Dec 5, 8:08 am, Robert wrote: Thank you for the advice. I managed to create my routings. The filter didnt do what it is supposed to do. Maybe beaceause of some inconsistent data. I second row, I replaced the codes with letters, so I could easily concatenate them. 1010479 L LKRM 1010479 K KRM 1010479 R RM 1010479 M M 1010490 H HSN HSN HSN 1010490 S SN SN 1010490 N N N What I now want is only the above in 2 new colums. 1010479 LKRM 1010490 HSN Is there an easy way to do is also? On 4 dec, 16:42, wrote: On Dec 4, 3:29 pm, wrote: On Dec 4, 2:58 pm, Robert wrote: I am having the following set of data. 14000 rows. A B 1010071 80425 1010071 80253 1010071 80425 1010071 80253 1010083 80451 1010083 80205 1010083 81853 1010083 80451 1010083 80205 1010083 81853 and so on... What I want to is this: Make a colum for C: Looking like this: 1010071 1010083 and so on.. = peace of cake and is done. But now is the problem. Behind this colum I want some colums holding the data for B. Looking like this 1010071 80425 80253 1010083 80451 80205 81853 80451 80205 81853 and so on.... I am having absolutely NO IDEA how to fix this one. any help would be welcome since doing this by hand is too much..I am pretty familiar with excel ( and a little with VBA) Thanks for any advice. Robert Hi 1.Do text to columns, using the space to seperate them. Soppose the data is now in columns A and B and starts in row 1 2. Type this formula in C1 =IF($A16=$A15,B16,"") 3. Drag the formula down column C 4. If there are any none blanks in column C, drag the formula to column D. Repeat until you have a column of blanks 5. Copy all your data and paste special...values to where it is, so that you remove the formulas. regards Paul- Hide quoted text - - Show quoted text - Oops Forgot to add the filtering bits: 0. Highlight the two columns, and do an advanced filter...choose "copy to another location" and select unique items. Choose the Copy To cell on a new sheet in A1 ... and 6. Highlight column A and do another advanced filter...choose "Filter the list in place" and select unique items 7. Copy the filtered data to a new location and you are done. regards Paul- Hide quoted text - - Show quoted text - |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Lookup function, but then a bit complicater
Eureka! It worked fine. Thank you sooo much :)
On 5 dec, 10:06, wrote: Hi Put column headings on your data. Select the first column only Do Filter, Advanced Filter, Filter in place and tick "Unique Items Only". Copy what you get to a new location and delete the columns you don't want. regards Paul On Dec 5, 8:08 am, Robert wrote: Thank you for the advice. I managed to create my routings. The filter didnt do what it is supposed to do. Maybe beaceause of some inconsistent data. I second row, I replaced the codes with letters, so I could easily concatenate them. 1010479 L LKRM 1010479 K KRM 1010479 R RM 1010479 M M 1010490 H HSN HSN HSN 1010490 S SN SN 1010490 N N N What I now want is only the above in 2 new colums. 1010479 LKRM 1010490 HSN Is there an easy way to do is also? On 4 dec, 16:42, wrote: On Dec 4, 3:29 pm, wrote: On Dec 4, 2:58 pm, Robert wrote: I am having the following set of data. 14000 rows. A B 1010071 80425 1010071 80253 1010071 80425 1010071 80253 1010083 80451 1010083 80205 1010083 81853 1010083 80451 1010083 80205 1010083 81853 and so on... What I want to is this: Make a colum for C: Looking like this: 1010071 1010083 and so on.. = peace of cake and is done. But now is the problem. Behind this colum I want some colums holding the data for B. Looking like this 1010071 80425 80253 1010083 80451 80205 81853 80451 80205 81853 and so on.... I am having absolutely NO IDEA how to fix this one. any help would be welcome since doing this by hand is too much..I am pretty familiar with excel ( and a little with VBA) Thanks for any advice. Robert Hi 1.Do text to columns, using the space to seperate them. Soppose the data is now in columns A and B and starts in row 1 2. Type this formula in C1 =IF($A16=$A15,B16,"") 3. Drag the formula down column C 4. If there are any none blanks in column C, drag the formula to column D. Repeat until you have a column of blanks 5. Copy all your data and paste special...values to where it is, so that you remove the formulas. regards Paul- Hide quoted text - - Show quoted text - Oops Forgot to add the filtering bits: 0. Highlight the two columns, and do an advanced filter...choose "copy to another location" and select unique items. Choose the Copy To cell on a new sheet in A1 ... and 6. Highlight column A and do another advanced filter...choose "Filter the list in place" and select unique items 7. Copy the filtered data to a new location and you are done. regards Paul- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Combining Lookup function and Sum function | Excel Worksheet Functions | |||
| LOOKUP FUNCTION? (LOOKUP VALUE BEING A TIME RENERATED FROM A FORMU | Excel Discussion (Misc queries) | |||
| Excel Data Validation/Lookup function does function correcty | Excel Worksheet Functions | |||
| how to combine an IF Function with a lookup function to determine | Excel Worksheet Functions | |||
| Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) | |||
![Robert[_33_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
