Lee,
I came up with a formula rather than a macro to handle this for you
I'm attaching the worksheet with a new tab "Actual Data_Test" that
created to test my formula. On that tab I deleted on of the rows data i
column "B", "C", and "D" to mimic your specs when a row may not have an
data.
In column "E" I created the following formula in "E2" and then copie
it down the range of data
"=IF(ISNA(MATCH(C2,$A$2:$A$38,0)+1),ROW(C2),MATCH(C2,$A$2:$A$38,0)+1)"
What this formula does is:
1st, with an "If" statement test via the "ISNA" to see if the "MATCH
formula can find the value in column "C" in the range of data in colum
"A". The "0" after the "$A$2:$A$38" requires the formula to find a
"Exact Match". And if the value is found, the the formula returns th
values relative position in the range "$A$2:$A$38". I added the "+1
because the values in column "A" start in row 2 and the "+1" allows th
returned value to match the row number where the value is found. This i
important mainly in the case that the value in "C" is blank.
2nd, If the formula did return the error "#N/A" the the formula return
the "ROW" number of that blank row, this is important so that th
sorting works correctly.
3rd, Is the copy of the first formula that is used in the event th
formula didn't return the "#N/A" error the formula is run and the valu
in "C" matches against column "A" and the values relative position i
returned (Plus 1).
So with this formula in place what you'd need to do to sort the data i
select all the data in column "B" through "E" and then click sort. Yo
will want to sort on column "E".
Also, when you get new data in the future you will need to adjust th
range reference in the formula "$A$2:$A$38", so that the second par
references that last cell in column "A". That needs to be done in bot
parts of th
formula...."=IF(ISNA(MATCH(C2,$A$2:*$A$38*,0)+1),ROW(C2),MATCH(C2,$A$2:*$A$38*,0)+1)"
But you only have to make the change in the first formula row, then yo
can copy the formula down to all the cells in column "E" and it shoul
work fine.
Let me know if you have any questions.
Good Luck,
Dan
Lee;483422 said:
All you wise and mighty Excel gurus... I have the attached
spreadsheet. I
will be scanning in the values (via a barcode scanner) into column A.
What
I then need is a macro that will leave the Column A values in the
order
that they appear, but then sort columns B,C and D to match the value
in
Column C to that of Column A. The number of rows may vary, so it would
be
nice to have something that an amateur like myself could modify (or
that
would work with any number of rows).
Thanks in advance!
Here is a visual example and the actual data (see the different
worksheet tabs)
www.progressivetel.com/~radney4/Scan.xl
+-------------------------------------------------------------------
|Filename: Scan_NewSortingFormula.xls
|Download:
http://www.thecodecage.com/forumz/attachment.php?attachmentid=265
+-------------------------------------------------------------------
