URGENT Please help with list alignment VBA

**saraspatula** · 04-11-2012, 11:28 PM

Hello, I am running Excel 2011 on a Mac and I am currently working on a systematic review paper of some scientific literature. I had to catalog a larger search as two smaller searches, and so I now have two separate lists that I have to align to the main list (which doesn't have all the supplementary data I need).

The main list that I want to align the data to is on the left. On the right are the two searches put together to make a master search. Unfortunately, there is a slight difference in the number of results in each list. The main (correct) list has 588, while the two searches put together have 592. My hope is that after alignment, the four entries that don't belong can just be thrown out. Here is how my spreadsheet is organized (row E is blank).

A---------B---------C----------D---------E--------F---------G-----------H--------I--------J--------...more data
1--------yr1----author1----journal1---|---------yr10----auth142----journal32-----PROJECT DATA
2--------yr1----author2----journal2---|---------yr5-----auth284----journal15-----PROJECT DATA
3--------yr2----author1----journal3---|---------yr9-----auth312----journal28-----PROJECT DATA
4--------yr3----author3----journal3---|---------yr12----auth13-----journal42-----PROJECT DATA

Every entry on the left has a match on the right, but not every entry on the right has a match on the left. Can someone please help me with a VBA program that will match the entries on their right to the correct order as shown on the left?

Thank you very much in advance!

**rylo** · 04-11-2012, 11:38 PM

Hi

I can't see that there is any relationship between the 2 list of data you have provided.

Can you attach a workbook example, showing both the before and after situations, and how that output was achieved.

rylo

**saraspatula** · 04-11-2012, 11:54 PM

Thanks for replying so quickly, rylo!

Attached is a workbook with an example of the formatting. The real version contains 588 (and 592) in the left and right lists respectively, but the idea is that the year, author, and journal have to be matched and then if they match, the entire row on the right has to be aligned with the corresponding row on the left. The color coding of the list on the right is very important and I need to keep that.

I am not sure of how to do this in VBA (or any way that works!) and I'd appreciate some help.

Thanks again for your help!

example first 41.xlsx

**rylo** · 04-12-2012, 12:07 AM

Hi

Using your example file:
E2: =SUMPRODUCT(--($B$2:$B$42=F2),--($C$2:$C$42=G2),--($D$2:$D$42=H2),ROW($D$2:$D$42))
Copy down to E42

Select the range E2:AE42
Data, Sort, Sort by column E, smallest to largest.

This will sort your data based on the row it should align against. Is this the correct order of the output? If so, then what do you want to do with the non matching items?

Sub aaa()
  lastrow = Cells(Rows.Count, 1).End(xlUp).Row
  Range("E2").Formula = "=SUMPRODUCT(--($B$2:$B$" & lastrow & "=F2),--($C$2:$C$" & lastrow & "=G2),--($D$2:$D$" & lastrow & "=H2),ROW($D$2:$D$" & lastrow & "))"
  Range("E2").AutoFill Destination:=Range("E2:E" & lastrow)
  Range("E2:AE" & lastrow).Sort key1:=Range("E2"), order1:=xlAscending
  cntzero = WorksheetFunction.CountIf(Range("E:E"), 0)
  Range("A2").Resize(cntzero, 4).Insert shift:=xlDown
  
  For i = 2 + cntzero To Cells(Rows.Count, 5).End(xlUp).Row
    If Cells(i, "E") <> i Then
      Cells(i, "E").Resize(1, 27).Insert shift:=xlDown
      Cells(i, "E").Resize(1, 27).Interior.ColorIndex = xlNone
    End If
  
  Next i
End Sub

rylo

**saraspatula** · 04-12-2012, 12:59 AM

Thank you so much for your help! It's getting late here, but I finally was able to get it done with your assistance.

Much appreciated!

saraspatula

URGENT Please help with list alignment VBA

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

URGENT Please help with list alignment VBA

Re: URGENT Please help with list alignment VBA

Re: URGENT Please help with list alignment VBA

Re: URGENT Please help with list alignment VBA

Re: URGENT Please help with list alignment VBA

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions