Sorting and Removing Single Records

[Trevor G]

I have a table in Excel which will show an Answer column, What I need to do here is first sort by smallest number, then loop through each row and anything that doesnt have a pair remove to a new sheet. There sometimes is nothing and on an odd occasion there maybe 3 or 4 records which will have the same number. The reason for keeping them as a pair, is that they come from to different systems and I have to identify one from the first system and then the other, so each system will have one of the pair. In the example it will show the entity as QIE but the other entity is further down the spreadsheet.

Example below:

Combined

ABCDE1EntityFirstOfPOLICY REFCheckCheck 1Answer2QIE005925/01/00003QIE010114/04/000020091029211566200910292115664QIE010597/02/000020090623183462200906231834625QIE012960/01/00006QIE014104/01/000020081126130953200811261309537QIE014184/01/000020081229138219200812291382198QIE014306/01/00009QIE014317/01/0000200812291382472008122913824710QIE014347/01/0000200812121353002008121213530011QIE014382/01/0000200812291382072008122913820712QIE014382/02/0000200812291382082008122913820813QIE014382/03/0000200812291382092008122913820914QIE014407/01/0000200812231375482008122313754815QIE014436/01/000016QIE014458/01/000017QIE014496/01/0000200903031552852009030315528518QIE014496/02/0000200903031552732009030315527319QIE014500/01/0000200901131413512009011314135120QIE014526/01/0000200901141415372009011414153721QIE014575/01/0000200901211434282009012114342822QIE014575/02/0000200901211434332009012114343323QIE014623/01/000024QIE014650/01/0000200811071242452008110712424525QIE014671/01/000026QIE014746/01/000027QIE014749/01/0000200902111492642009021114926428QIE014755/01/000029QIE014805/01/000030QIE014867/01/000031QIE014870/01/000032QIE014881/01/000033QIE014882/01/000034QIE014882/02/000035QIE014901/01/0000
Spreadsheet FormulasCellFormulaC2=IF(ISERROR(VLOOKUP(B2,DBO,2,FALSE)),"",VLOOKUP(B2,DBO,2,FALSE))D2=IF(ISERROR(VLOOKUP(B2,Iris_Phoneix,2,FALSE)),"",VLOOKUP(B2,Iris_Phoneix,2,FALSE))E2=IF(D2<>"",D2,IF(C2<>"",C2,""))C3=IF(ISERROR(VLOOKUP(B3,DBO,2,FALSE)),"",VLOOKUP(B3,DBO,2,FALSE))D3=IF(ISERROR(VLOOKUP(B3,Iris_Phoneix,2,FALSE)),"",VLOOKUP(B3,Iris_Phoneix,2,FALSE))E3=IF(D3<>"",D3,IF(C3<>"",C3,""))C4=IF(ISERROR(VLOOKUP(B4,DBO,2,FALSE)),"",VLOOKUP(B4,DBO,2,FALSE))D4=IF(ISERROR(VLOOKUP(B4,Iris_Phoneix,2,FALSE)),"",VLOOKUP(B4,Iris_Phoneix,2,FALSE))E4=IF(D4<>"",D4,IF(C4<>"",C4,""))C5=IF(ISERROR(VLOOKUP(B5,DBO,2,FALSE)),"",VLOOKUP(B5,DBO,2,FALSE))D5=IF(ISERROR(VLOOKUP(B5,Iris_Phoneix,2,FALSE)),"",VLOOKUP(B5,Iris_Phoneix,2,FALSE))E5=IF(D5<>"",D5,IF(C5<>"",C5,""))C6=IF(ISERROR(VLOOKUP(B6,DBO,2,FALSE)),"",VLOOKUP(B6,DBO,2,FALSE))D6=IF(ISERROR(VLOOKUP(B6,Iris_Phoneix,2,FALSE)),"",VLOOKUP(B6,Iris_Phoneix,2,FALSE))E6=IF(D6<>"",D6,IF(C6<>"",C6,""))C7=IF(ISERROR(VLOOKUP(B7,DBO,2,FALSE)),"",VLOOKUP(B7,DBO,2,FALSE))D7=IF(ISERROR(VLOOKUP(B7,Iris_Phoneix,2,FALSE)),"",VLOOKUP(B7,Iris_Phoneix,2,FALSE))E7=IF(D7<>"",D7,IF(C7<>"",C7,""))C8=IF(ISERROR(VLOOKUP(B8,DBO,2,FALSE)),"",VLOOKUP(B8,DBO,2,FALSE))D8=IF(ISERROR(VLOOKUP(B8,Iris_Phoneix,2,FALSE)),"",VLOOKUP(B8,Iris_Phoneix,2,FALSE))E8=IF(D8<>"",D8,IF(C8<>"",C8,""))C9=IF(ISERROR(VLOOKUP(B9,DBO,2,FALSE)),"",VLOOKUP(B9,DBO,2,FALSE))D9=IF(ISERROR(VLOOKUP(B9,Iris_Phoneix,2,FALSE)),"",VLOOKUP(B9,Iris_Phoneix,2,FALSE))E9=IF(D9<>"",D9,IF(C9<>"",C9,""))C10=IF(ISERROR(VLOOKUP(B10,DBO,2,FALSE)),"",VLOOKUP(B10,DBO,2,FALSE))D10=IF(ISERROR(VLOOKUP(B10,Iris_Phoneix,2,FALSE)),"",VLOOKUP(B10,Iris_Phoneix,2,FALSE))E10=IF(D10<>"",D10,IF(C10<>"",C10,""))C11=IF(ISERROR(VLOOKUP(B11,DBO,2,FALSE)),"",VLOOKUP(B11,DBO,2,FALSE))D11=IF(ISERROR(VLOOKUP(B11,Iris_Phoneix,2,FALSE)),"",VLOOKUP(B11,Iris_Phoneix,2,FALSE))E11=IF(D11<>"",D11,IF(C11<>"",C11,""))C12=IF(ISERROR(VLOOKUP(B12,DBO,2,FALSE)),"",VLOOKUP(B12,DBO,2,FALSE))D12=IF(ISERROR(VLOOKUP(B12,Iris_Phoneix,2,FALSE)),"",VLOOKUP(B12,Iris_Phoneix,2,FALSE))E12=IF(D12<>"",D12,IF(C12<>"",C12,""))C13=IF(ISERROR(VLOOKUP(B13,DBO,2,FALSE)),"",VLOOKUP(B13,DBO,2,FALSE))D13=IF(ISERROR(VLOOKUP(B13,Iris_Phoneix,2,FALSE)),"",VLOOKUP(B13,Iris_Phoneix,2,FALSE))E13=IF(D13<>"",D13,IF(C13<>"",C13,""))C14=IF(ISERROR(VLOOKUP(B14,DBO,2,FALSE)),"",VLOOKUP(B14,DBO,2,FALSE))D14=IF(ISERROR(VLOOKUP(B14,Iris_Phoneix,2,FALSE)),"",VLOOKUP(B14,Iris_Phoneix,2,FALSE))E14=IF(D14<>"",D14,IF(C14<>"",C14,""))C15=IF(ISERROR(VLOOKUP(B15,DBO,2,FALSE)),"",VLOOKUP(B15,DBO,2,FALSE))D15=IF(ISERROR(VLOOKUP(B15,Iris_Phoneix,2,FALSE)),"",VLOOKUP(B15,Iris_Phoneix,2,FALSE))E15=IF(D15<>"",D15,IF(C15<>"",C15,""))C16=IF(ISERROR(VLOOKUP(B16,DBO,2,FALSE)),"",VLOOKUP(B16,DBO,2,FALSE))D16=IF(ISERROR(VLOOKUP(B16,Iris_Phoneix,2,FALSE)),"",VLOOKUP(B16,Iris_Phoneix,2,FALSE))E16=IF(D16<>"",D16,IF(C16<>"",C16,""))C17=IF(ISERROR(VLOOKUP(B17,DBO,2,FALSE)),"",VLOOKUP(B17,DBO,2,FALSE))D17=IF(ISERROR(VLOOKUP(B17,Iris_Phoneix,2,FALSE)),"",VLOOKUP(B17,Iris_Phoneix,2,FALSE))E17=IF(D17<>"",D17,IF(C17<>"",C17,""))C18=IF(ISERROR(VLOOKUP(B18,DBO,2,FALSE)),"",VLOOKUP(B18,DBO,2,FALSE))D18=IF(ISERROR(VLOOKUP(B18,Iris_Phoneix,2,FALSE)),"",VLOOKUP(B18,Iris_Phoneix,2,FALSE))E18=IF(D18<>"",D18,IF(C18<>"",C18,""))C19=IF(ISERROR(VLOOKUP(B19,DBO,2,FALSE)),"",VLOOKUP(B19,DBO,2,FALSE))D19=IF(ISERROR(VLOOKUP(B19,Iris_Phoneix,2,FALSE)),"",VLOOKUP(B19,Iris_Phoneix,2,FALSE))E19=IF(D19<>"",D19,IF(C19<>"",C19,""))C20=IF(ISERROR(VLOOKUP(B20,DBO,2,FALSE)),"",VLOOKUP(B20,DBO,2,FALSE))D20=IF(ISERROR(VLOOKUP(B20,Iris_Phoneix,2,FALSE)),"",VLOOKUP(B20,Iris_Phoneix,2,FALSE))E20=IF(D20<>"",D20,IF(C20<>"",C20,""))C21=IF(ISERROR(VLOOKUP(B21,DBO,2,FALSE)),"",VLOOKUP(B21,DBO,2,FALSE))D21=IF(ISERROR(VLOOKUP(B21,Iris_Phoneix,2,FALSE)),"",VLOOKUP(B21,Iris_Phoneix,2,FALSE))E21=IF(D21<>"",D21,IF(C21<>"",C21,""))C22=IF(ISERROR(VLOOKUP(B22,DBO,2,FALSE)),"",VLOOKUP(B22,DBO,2,FALSE))D22=IF(ISERROR(VLOOKUP(B22,Iris_Phoneix,2,FALSE)),"",VLOOKUP(B22,Iris_Phoneix,2,FALSE))E22=IF(D22<>"",D22,IF(C22<>"",C22,""))C23=IF(ISERROR(VLOOKUP(B23,DBO,2,FALSE)),"",VLOOKUP(B23,DBO,2,FALSE))D23=IF(ISERROR(VLOOKUP(B23,Iris_Phoneix,2,FALSE)),"",VLOOKUP(B23,Iris_Phoneix,2,FALSE))E23=IF(D23<>"",D23,IF(C23<>"",C23,""))C24=IF(ISERROR(VLOOKUP(B24,DBO,2,FALSE)),"",VLOOKUP(B24,DBO,2,FALSE))D24=IF(ISERROR(VLOOKUP(B24,Iris_Phoneix,2,FALSE)),"",VLOOKUP(B24,Iris_Phoneix,2,FALSE))E24=IF(D24<>"",D24,IF(C24<>"",C24,""))C25=IF(ISERROR(VLOOKUP(B25,DBO,2,FALSE)),"",VLOOKUP(B25,DBO,2,FALSE))D25=IF(ISERROR(VLOOKUP(B25,Iris_Phoneix,2,FALSE)),"",VLOOKUP(B25,Iris_Phoneix,2,FALSE))E25=IF(D25<>"",D25,IF(C25<>"",C25,""))C26=IF(ISERROR(VLOOKUP(B26,DBO,2,FALSE)),"",VLOOKUP(B26,DBO,2,FALSE))D26=IF(ISERROR(VLOOKUP(B26,Iris_Phoneix,2,FALSE)),"",VLOOKUP(B26,Iris_Phoneix,2,FALSE))E26=IF(D26<>"",D26,IF(C26<>"",C26,""))C27=IF(ISERROR(VLOOKUP(B27,DBO,2,FALSE)),"",VLOOKUP(B27,DBO,2,FALSE))D27=IF(ISERROR(VLOOKUP(B27,Iris_Phoneix,2,FALSE)),"",VLOOKUP(B27,Iris_Phoneix,2,FALSE))E27=IF(D27<>"",D27,IF(C27<>"",C27,""))C28=IF(ISERROR(VLOOKUP(B28,DBO,2,FALSE)),"",VLOOKUP(B28,DBO,2,FALSE))D28=IF(ISERROR(VLOOKUP(B28,Iris_Phoneix,2,FALSE)),"",VLOOKUP(B28,Iris_Phoneix,2,FALSE))E28=IF(D28<>"",D28,IF(C28<>"",C28,""))C29=IF(ISERROR(VLOOKUP(B29,DBO,2,FALSE)),"",VLOOKUP(B29,DBO,2,FALSE))D29=IF(ISERROR(VLOOKUP(B29,Iris_Phoneix,2,FALSE)),"",VLOOKUP(B29,Iris_Phoneix,2,FALSE))E29=IF(D29<>"",D29,IF(C29<>"",C29,""))C30=IF(ISERROR(VLOOKUP(B30,DBO,2,FALSE)),"",VLOOKUP(B30,DBO,2,FALSE))D30=IF(ISERROR(VLOOKUP(B30,Iris_Phoneix,2,FALSE)),"",VLOOKUP(B30,Iris_Phoneix,2,FALSE))E30=IF(D30<>"",D30,IF(C30<>"",C30,""))C31=IF(ISERROR(VLOOKUP(B31,DBO,2,FALSE)),"",VLOOKUP(B31,DBO,2,FALSE))D31=IF(ISERROR(VLOOKUP(B31,Iris_Phoneix,2,FALSE)),"",VLOOKUP(B31,Iris_Phoneix,2,FALSE))E31=IF(D31<>"",D31,IF(C31<>"",C31,""))C32=IF(ISERROR(VLOOKUP(B32,DBO,2,FALSE)),"",VLOOKUP(B32,DBO,2,FALSE))D32=IF(ISERROR(VLOOKUP(B32,Iris_Phoneix,2,FALSE)),"",VLOOKUP(B32,Iris_Phoneix,2,FALSE))E32=IF(D32<>"",D32,IF(C32<>"",C32,""))C33=IF(ISERROR(VLOOKUP(B33,DBO,2,FALSE)),"",VLOOKUP(B33,DBO,2,FALSE))D33=IF(ISERROR(VLOOKUP(B33,Iris_Phoneix,2,FALSE)),"",VLOOKUP(B33,Iris_Phoneix,2,FALSE))E33=IF(D33<>"",D33,IF(C33<>"",C33,""))C34=IF(ISERROR(VLOOKUP(B34,DBO,2,FALSE)),"",VLOOKUP(B34,DBO,2,FALSE))D34=IF(ISERROR(VLOOKUP(B34,Iris_Phoneix,2,FALSE)),"",VLOOKUP(B34,Iris_Phoneix,2,FALSE))E34=IF(D34<>"",D34,IF(C34<>"",C34,""))C35=IF(ISERROR(VLOOKUP(B35,DBO,2,FALSE)),"",VLOOKUP(B35,DBO,2,FALSE))D35=IF(ISERROR(VLOOKUP(B35,Iris_Phoneix,2,FALSE)),"",VLOOKUP(B35,Iris_Phoneix,2,FALSE))E35=IF(D35<>"",D35,IF(C35<>"",C35,""))

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

Trevor

 

[Trevor G]

I have noticed the html code I tried to add to show you the table, doesn't appear correctly, so I have done a screen shot.

If anyone can help that will be great.

Thank you

Trevor

 

[Brianwarnock]

Why don't you attach your sheet so that somebody can play with it.
Am I correct in thinking that rows 4 5 10 are candidates?
If there are 3 or 4 , can there be more, what do you want to happen?

Brian

 

[Trevor G]

Yes Brian you are correct that 4, 5 and 10 don't match as pairs.

I have attached an extract of the spreadsheet. Within it there will always be an entity of 2 halfs, ie first qie and then 386, the answer column value will match the one below or above depending on how you look at it.


I have highlighted in red the rows that don't match, and I would like the entire row to be extracted to a new sheet not deleted as someone will have to take on checking why there is no match in the 2 systems (Entity).

I hope this can be resolved.

Thank you for taking the time to look over this.

Trevor

 

[Brianwarnock]

I have attached your workbook with 2 changes
1 I have copied combined to Sheet1 as my macro had been written but not posted before your reply, I also noticed that Answer had moved from Col E to Col D !!

2 I have included the macro

The rows to be copied, to Sheet2, and then deleted were not highlighted.

It is easy to amend the macro to say include the headings on Sheet2, however I am not convinced that my macro is the solution as earlier you say that there can be 1 2 3 or 4 records with the same answer, you did not say how you wanted this handled, but worse how do you know the sort will pair them correctly, I assumed that the macro was to do the sorting on Col D as per post 1, if not remove those 2 lines of code.

Are you sure that this should not be done in Access with the data from the 2 files being in 2 tables, Access is designed to handle duplicates and record matching between tables.

Brian

 

[Trevor G]

Hi Brian,

Thank you for taking the time to look at my spreadsheet, I removed the some data as it will be sensitive to the business so I didn't want to leave this on the net. I removed the data which related to the policy numbers so this moved the column to the left.

I have run it but it stopped at the following line:

b = .Cells(currentrow - 1, 5).Value
it suggest run time error 1004, Application Defined or object defined error

My reason for not continuing this in Access, is because I am taking the data from a number of different systems each time I try and relate the records in a query I am not bring back the correct data, I also have to sums and percentages based on row, compared to the other and then alternate this down the results, Access doesn't give me this facility.

The Management team aren't allowed Access (and they don't have the knowledge) so they take this into Excel for them to do there own thing with the results.

I am close to concluding this task but have a few things left to do.

I am grateful for your time and expertise.

Kind regards

Trevor

 

[Trevor G]

Brian,

Thank you for your assistance, I have now managed to complete this side of the task.

Kind regards

Trevor

 

[Brianwarnock]

I don't know how it happened but the version of the macro in the posted spreadsheet was still looking at column E, and also contained 2 commented out statements
Still I gather from your last post that you have sorted it, which is a good thing because it means that you understand the code.

You did not answer the question regarding more than 2 records with the same answer in post#1 you said

on an odd occasion there maybe 3 or 4 records which will have the same number

Brian

 

[Trevor G]

What I ended up doing is I inserted an extra column, used a countif formula to check for how many entries there are on the Answer which gave a me a value of either 1 or 2 or more than that, then I filtered the data and copied everything which was 2 only, then wrote my if statement to work out the split to see if it meet the company requirements and took the values to a separate sheet.

Then I had to write all of this in code, which has now been done. My knowledge is growing each day, but occassionally I get stuck and need to step back and then re apply.

Every new way is a good learning curve for a 49 year old.....

Thanks again Brian.

Kind regards

Trevor

 

[Brianwarnock]

Every new way is a good learning curve for a 49 year old.....

Thanks again Brian.

Kind regards

Trevor

It should be easy for a youngster like you,

Best of luck with the rest of the project

Brian