Expert: Bob Umlas - 11/3/2009

Question
QUESTION: hello....you got my last que...i think so..

i have another problem in the same macro..

Once if from "Sale" sheet 5 rows merged in soda sheet's respective row(lik as per orderno matches)

now when i enter new rows in sale ... 5 was there and i m entering another 3 rows..

so this time i only want the newly added 3 rows... but i m getting all 8 rows..

so how to get this problem solved?

plz reply..

thnx so much..


ANSWER: I dno't understand the question. Also I don't keep files sent to me from AllExperts. Also I don't remember the problem as I get LOTS of email from this site.

---------- FOLLOW-UP ----------

QUESTION: Here i am giving you all the details from begining..
first of all thank you for answering..

1st Question i hv asked to you:

"hello sir,,

i have two sheet....1) soda 2) sale

Now in sheet1(soda).. there is a column named order No...
In second sheet(sale) there are multiple entries on that order no..

like.. for order no.111 there is only one row in sheet1, there are 1 or more than 1 row in sheet2...(may be 10 or 15)...

now what i want is.. i want data of both sheets together based on order no...

how to get it..

i cn copy rows from sheet2 based on order no..and add them to sheet1 is it posible? how?

or may be i can add all the data of many rows in only one row in sheet1 having that order no..

can u help me plz on this??

thanx..."

Answer
I'm not sure if you're looking to add the results of sale onto soda or if you want to copy all the rows from sale onto soda - if the latter, then where? You can set up sale to use autofilter and filter just by the order # chosen in soda, then you can copy the visible cells from the filter onto the soda sheet. Does this help?

QUESTION: thanx a lot...

but i cant copy ...likewise...because its so much time consuming....data is in thousands of rows..

i m explaining again...it may help u to understand what i want is..

sheet1(soda)

Date    orderno   Ordered-Quantity   Pending
28/10     111           500            150


sheet2(sale)

Date   Orderno  OrderedQuantity SoldQuantity  invoiceno
28/10    111        500             200         a1
28/10    112        100             100         a2
29/10    111        500             150         a3


now what i want is... to fetch rows from sale sheet onto soda sheet based on orderno...but i want it using macro or formula,,,as its nt posible to copy and paste...cz of bulky data

now soda sheet would be like...
possibility-1

date  orderno  orderedquantity  pending
28/10   111        500           150

(now sale detail)
28/10   111        200      a1
29/10   111        150      a3  

pos-2
date  orderno orderquan pending date  soldquan invno date  soldquan inv
28/10   111      500     150    28/10    200     a1  29/10   150     a3  

Answer
Assuming the word "Date" is on the Soda Sheet in cell A1, and the other data in Sale starts in A1 as well:

Sub vrunda()
  Dim N As Integer, M As Integer
  On Error Resume Next
  For i = 2 To Sheets("Soda").Range("B50000").End(xlUp).Row
      M = 1
      orderno = Sheets("Soda").Cells(i, 2).Value
      Do
          N = 0
          N = Sheets("Sale").Cells(M, 2).Resize(30000).Find(orderno, lookat:=xlWhole).Row
          If N = 0 Then GoTo NextOne
          Sheets("Sale").Cells(N, 1).Resize(1, 4).Copy Sheets("Soda").Cells(i, 256).End(xlToLeft).Offset(0, 1)
          M = N + 1
      Loop
NextOne:
  Next
End Sub

Now The Question is:

Hey Mr.Bob Ur solution of to get multiple rows from another sheet based on cell value is running perfectly,,,,

can u plz edit macro given by you..

now i need to fetch data from two different sheets "tax" and "retail" instead of only "sale" sheet...

all another details are similar..

and yes orderno for example, '111' is either in "tax" or "retail" not in both..

plz help me for this..

there will be multiple rows of 111 but,,, all are either in "tax" or in "retail" just we have to find where it is...

looking forward to get the macro..

with regards..

i have another problem in the same macro..

Once if from "Sale" sheet 5 rows copied in soda sheet's respective row(like as per orderno matches)

now when i enter new rows in sale ... 5 was there and i m entering another 3 rows..

so this time i only want the newly added 3 rows... but i m getting all 8 rows..

so how to get this problem solved?

plz reply..

Answer
Sub vrunda()
 Dim N As Integer, M As Integer
 On Error Resume Next
 For each SH in array("Sale","Tax","Retail")
 For i = 2 To Sheets("Soda").Range("B50000").End(xlUp).Row
     M = 1
     orderno = Sheets("Soda").Cells(i, 2).Value
     Do
         N = 0
         N = Sheets(SH).Cells(M, 2).Resize(30000).Find(orderno, lookat:=xlWhole).Row
         If N = 0 Then GoTo NextOne
         Sheets(SH).Cells(N, 1).Resize(1, 4).Copy Sheets("Soda").Cells(i, 256).End(xlToLeft).Offset(0, 1)
         M = N + 1
     Loop
NextOne:
 Next
 Next
End Sub

Regarding the 2nd issue - instead of adding new rows in sale and rerunning the macro, which was designed as per your instructions, why not just put it in the format you need in the first place?
There's no way excel "knows" which ones were done already.