PDA

View Full Version : [SOLVED] Search value and insert row under value



danovkos
04-05-2009, 11:47 PM
hi all,
i posted this in other forum, but there nobody helps me:
in in links:
http://www.excelforum.com/excel-programming/678392-search-value-and-insert-row-under-value.html#post2070455

can you help me with this?
i search a macro which will do following.

I have a range of cells (old data) and other range of data (new data - those are locatet under the old data). I want after i select my new range of data (only yellow data) and running macro look in to the old data (blue) where can by blank row too and looking for the same id (id_new=id). If it found any, it will insert row under the last equal ID value (in old, yellow data) and copy the new data (whole row) in the created row in new data (blue)
Here is atach. with 2 sheet. Sheets with name "Before" and "after".

I want to use it for a huge data (about 6000 rows and 80 columns)
The range of old data will be always ends 5 rows upper the new data
Please how can i do this?

MaximS
04-07-2009, 06:40 PM
try my solution, see attached for details:

danovkos
04-07-2009, 11:39 PM
waauu :)
fantastic...:clap:
thank you very very much

pls. only last question
I will try it customize for my data to column C and D but this will not works :(
what is wrong pls. ?


Dim ORow, NRow, x, y As Long
Dim rng As Range
With Sheets("Before")
NRow = .Range("C65536").End(xlUp).Row
ORow = .Range("C" & NRow).End(xlUp).Row - 6
x = ORow + 7
y = NRow

For i = ORow To 2 Step -1
Set rng = .Range("C" & x & ":C" & y)
temp = Cells(i, 1).Value
If temp <> Cells(i + 1, 1).Value And temp <> "" Then
On Error Resume Next
check = WorksheetFunction.Match(temp, rng, 0)
On Error GoTo 0
If check <> "" Then
If Cells(i + 1, 1).Value = "" Then
.Range("C" & i + 1 & ":D" & i + 1).Value = _
.Range("C" & x - 1 + check & ":D" & x - 1 + check).Value
check = ""
Else
.Rows(i + 1).Insert
.Range("C" & i + 1 & ":D" & i + 1).Value = _
.Range("C" & x + check & ":D" & x + check).Value
check = ""
x = x + 1
y = y + 1
End If
End If
End If
Next i
End With
End Sub

danovkos
04-08-2009, 12:12 AM
and pls. one question,
how can i insert more as one row. F.e. If i have in new data 2 or more rows with the same ID, (there is more rows in new data with the same ID1 but the ID2 are diferent and all will be inserted). So it will insert more rows. Can it possible?
thx a lot

danovkos
04-09-2009, 01:52 AM
HI all,
i tried it change, that it will works for column C and D, but something is wrong. And the really last condition what will really help me is insert more as one row if is neccessary. As is in atach.
Pls. can you help me?
thx a lot.

MaximS
04-10-2009, 11:05 PM
that should be more understandable and easier for adjustment:



Dim ORow, NRow, x, y As Long

Dim rng As Range
With Sheets("Before")
NRow = .Range("C65536").End(xlUp).Row
ORow = .Range("C" & NRow).End(xlUp).Row - 6
x = ORow + 7
y = NRow

For i = ORow To 2 Step -1
Set rng = .Range("C" & x & ":C" & y)
temp = .Range("C" & i).Value
If temp <> .Range("C" & i + 1).Value And temp <> "" Then
On Error Resume Next
check = WorksheetFunction.Match(temp, rng, 0)
On Error GoTo 0
If check <> "" Then
If .Range("C" & i + 1).Value = "" Then
.Range("C" & i + 1 & ":D" & i + 1).Value = _
.Range("C" & x - 1 + check & ":D" & x - 1 + check).Value
check = ""
Else
.Rows(i + 1).Insert
.Range("C" & i + 1 & ":D" & i + 1).Value = _
.Range("C" & x + check & ":D" & x + check).Value
check = ""
x = x + 1
y = y + 1
End If
End If
End If
Next i
End With
End Sub

MaximS
04-11-2009, 05:31 AM
i have reviewed my code and this should work better:



Private Sub Updater()
Dim TLRow, BLRow, x, y, z As Long
Dim rng, rng1 As Range
With Sheets("before")
BLRow = .Range("C65536").End(xlUp).Row
TLRow = .Range("C" & BLRow).End(xlUp).Row - 6
x = TLRow + 7
y = BLRow
z = TLRow
.Range("IV2:IV" & BLRow).Formula = "=C2&"" ""&D2"
c = x
Do Until c > y
Set rng = .Range("C1:C" & z)
Set rng1 = .Range("IV1:IV" & z)
On Error Resume Next
k = WorksheetFunction.Match(.Range("C" & c).Value, rng, 0)
m = WorksheetFunction.Match(.Range("IV" & c).Value, rng1, 0)
On Error GoTo 0
If k <> "" Then
If m = "" Then
Do Until n <> p
n = .Range("C" & k).Value
On Error Resume Next
p = CInt(Left(.Range("IV" & c).Value, Len(n)))
On Error GoTo 0
k = k + 1
Loop
k = k - 1
If .Range("C" & k).Value = "" And k < z Then
.Range("C" & k & ":D" & k).Value = _
.Range("C" & c & ":D" & c).Value
Else
.Rows(k).Insert
c = c + 1
.Range("C" & k & ":D" & k).Value = _
.Range("C" & c & ":D" & c).Value
x = x + 1
y = y + 1
z = z + 1
End If
End If
Else
z = z + 1
If .Range("C" & z).Value = " " Or _
.Range("C" & z).Value = "" Then
.Rows(z).Insert
c = c + 1
.Range("C" & z & ":D" & z).Value = _
.Range("C" & c & ":D" & c).Value
x = x + 1
y = y + 1
End If
End If
c = c + 1
k = ""
m = ""
n = ""
p = ""
Loop
.Columns("IV").ClearContents
End With
End Sub

danovkos
04-13-2009, 11:33 PM
Yes, this works good, only one last thing. I need the new inserted rows at the end of group of IDs. Now it insert at the start. If can we this change it will be perfect.

Thank you for your big help.

MaximS
04-14-2009, 01:01 AM
Can you explain what do you mean by 'end of group of IDs' ?

If possible attach sample (before/after).

danovkos
04-14-2009, 01:32 AM
Is it exactly as it was in my last atach. „testx2“
But the new code insert the new data (rows) not below the data with the same ID, but at the start.

F.e. in the atach. in new data is ID new 7 and ID2 new is 777 and second value is ID NEW 7 and second value 778.
I want insert those two rows after ID 7 and ID2 79 – because this is the last row from grup with ID 7.

I hope i am cleary, because i know, that my english is bad :(.
but thx for you patience

MaximS
04-14-2009, 03:03 AM
try now:

danovkos
04-14-2009, 03:39 AM
i dont know if the sorting can help.
My data as i wrote are huge and there i can not sort because it will mix my data. Your last code works only for the example atach, but if i insert there data, which are in my DB, it will not alway work. Look at atach. this is as my data looks.

MaximS
04-14-2009, 04:48 AM
try again:

mdmackillop
04-14-2009, 05:39 AM
Hi Maxim,
A couple of comments on your code.
Always use Option Explicit. You have a few undeclared variables
You should Dim each variable separately Dim x, y as Long, only Dims Y as long, X is a variant. "" is not the same as 0, and cannot be assigned to a Long variable.
Try


Option Explicit
Private Sub Updater()
Dim TLRow, BLRow, x As Long, y As Long, z As Long
Dim rng As Range, rng1 As Range
x = 3
y = 4
z = 5
x = 0
y = "" 'Code fails here.
z = ""
End Sub

MaximS
04-14-2009, 06:11 AM
hi mdmackilop,

I am always using Option Explicit and variable declarations bearing in mind their type, but at this time i've tried to give an answer as quick as possible while doing 2 projects (one for work and this for pleasure).

Thats why some of the variables are having declaration missing.

Anyway what you saying is perfectly right.

danovkos
04-14-2009, 07:19 AM
Sorry guys, but it doesnt works for my huge DB. In file it works good, but if i try it in my DB it will insert new rows in the begining of IDs group (not under the data with the same ID1). I would like to send you my DB but this is secret data about of 6000 rows and 90 columns. :(
Can you figured out, where the probleme is without my DB?

MaximS
04-14-2009, 08:13 AM
What do you mean by DB? Proper database or spreadsheet??

If it is Excel spreadsheet make a copy of it and then overwrite with random data everything except the part you willing to process i.e columns C and D.

danovkos
04-16-2009, 02:14 AM
yes it is excel workbook. But the data i can not send it here because it are account number and clients IDs :(

MaximS
04-16-2009, 07:38 AM
understandable, can you then post example of about 10 records with names, ids, acc numbers, etc. replaced with just a format type (i.e. acc number xxxx-xxxx)?

danovkos
04-16-2009, 12:37 PM
OK, i will try it. But now i am ill at home. I will post it on tuesday. But thank you for now.

danovkos
04-21-2009, 07:02 AM
hi all
so i atached a big file with fake data.
My original excel file is a much bigger, but here is a size limit. So i deleted some rows and columns. There are some hidden columns and sometimes rows too when i used this DB (excel workbook).

In this file code doesnt works. It insert the row with new data on the start of ID group as i wrote earlier.

Do you know why? Because in small file with a few data it works good.

thank you very very much.

MaximS
04-21-2009, 09:05 AM
hi there,

i think now is everything clear after seeing the data and I hope I've found solution for you.

see attached for update.

danovkos
04-21-2009, 10:43 PM
thank you, but it still doesnt works for me :dunno
maybe i do something wrong ?
what i did,
i selected my new data (in red C1642:D1646) and run macro
and it insert rows again at the start/begining of group IDs
it look like this:


1234579 11111111 new data
1234579 22222222 new data
1234579 2345678963 old data
1234579 2345678963 old data


and i need it like this (old data are first and then are inserted the new data (new data are IDs1 with ID2 - 1111111 and 22222222


1234579 2345678963 old data
1234579 2345678963 old data
1234579 11111111 new data
1234579 22222222 new data


maybe i am not clear...

works it for you good (as i want) or it do it to you the same?

MaximS
04-25-2009, 12:15 AM
now you perfectly clear will fix it

MaximS
04-25-2009, 06:23 AM
there we go - should be fine now:

danovkos
04-27-2009, 12:46 AM
so, i tried it. In this file it works but in my huge DB no :(.
I know, that this is hard to figured out what is wrong, because you can not work with real data...maybe is it in my hide rows or columns, or in more data...or i dont know :(

MaximS
04-27-2009, 01:00 AM
so what is actually not working or going wrong??

danovkos
04-27-2009, 02:25 AM
ok, i figured out, where is a problem. It works only if is there minimum 1 column with value in cell till end of whole table. If is there somewhere in table blank row, the code works only for this blank row and not for whole table.
here atach.

danovkos
04-27-2009, 02:35 AM
correction:
doesnt help if i make one column with data in each cell from start till end of table. I looks, that makes problem the first blank cell in column

danovkos
04-27-2009, 02:38 AM
correction:
maybe doesnt help if i insert data in whole column (from start till end) because i did it and it doesnt works. It works only for 1 part of table. The first part ends with any blank cells - mostly is blank whole row.

MaximS
04-27-2009, 02:47 AM
just to clarify - do you want to update everything up to row 1561 or up to 1077?

if up to 1561 what do you want to do witch id1 which cannot be found in them 3 sections (put at the bottom of 3rd section or leave)?

danovkos
04-27-2009, 03:27 AM
i want to update all data above my new data (if i have writen new id and id2 in row 6000 it will look and update all from 1 to 5995). If cannot be found, it will put it after last part non blank cell in column C but up of new data (in red)

MaximS
04-27-2009, 04:20 AM
Ok finaly got you and hope this time solution will meet your requirements.


See attachments for details.

danovkos
04-27-2009, 04:47 AM
YES, YES, YEEEEEES :))))
THANK YOU VERY VERY MUCH...IT works great excatly what i need :)

thank you for you patience, for you time. I know, that is too complicatet with me :)

have a nice day

danovkos
05-04-2009, 06:30 AM
HI all :(. Pls. to this code little question yet. How it works, because i dont understund it from code. And now i add new data to my baqsic table (old data) with other color (green) and the CODE insert all new data (data under the basic table - id2) at the end of previous end of table (orange font color). And it will not looking in my new added basic table. I expand the basic table every month. I don know if i am clear :(

danovkos
05-04-2009, 06:51 AM
i atached file

danovkos
05-04-2009, 06:56 AM
ps: if is neccessary to define the end of basic data (old data) i can do this - f.e. i will write to end of table "end_table" if it helps to define where the CODE have to looking for.

danovkos
05-05-2009, 05:34 AM
I tried it all the day figured out, why this code works only for existing data. I mean that it insert id2(new data) only for row 1568. But if i add new basic data, so i extend existing basic DB adding data from row f.e.: 1574 to 1600 and between this old basic DB and the extended part is blank row, it dosnt care about this new rows. :(
WHY pls?
thank you

danovkos
05-05-2009, 11:18 PM
i got it :)
i replace in this code
this one


For i = 1 To BLRow
If Cells(i, "C").Font.ColorIndex = 46 Then Exit For
Next i

with this one



For i = 1 To BLRow
If Cells(i, "C").Value = "endOFtable" Then Exit For
Next i