Extracting data from one list and sorting it into another

[frankshank]

Hi All,

I am having a problem with the extraction of data from one list to another. The problem that keeps coming up is that I have a master list for some data that I am attempting to take a number of smaller lists to add onto it. The problem is that the smaller lists do not have the full identifiers that the master list does. I need help with the sorting and matching the attributable information to the respective identifier.

For example, I have the master list below:

Neighborhood | All Cars (count)
1001 | 4
1002 | 15
1003 | 12
1004 | 2
1005 | 7
1006 | 22

Then I have a smaller list that I want to add to the master list:

Neighborhood | Trucks (count)
1003 | 5
1005 | 2
1006 | 7

*Notice how the list isn't comprehensive of all the neighborhoods, there isn't a full selection of all of them.

Ideally, what I would like to have would be a list that looks like this:

Neighborhood | All Cars (count) | Trucks (count)
1001 | 4 | 0
1002 | 15 | 0
1003 | 12 | 5
1004 | 2 | 0
1005 | 7 | 2
1006 | 22 | 7

But, the problem that ends up happening is found below:

Neighborhood | All Cars (count) | Trucks (count)
1001 | 4 | 5
1002 | 15 | 2
1003 | 12 | 7
1004 | 2 |
1005 | 7 |
1006 | 22 |

Does anyone know how to sample the data so that it automatically will take the smaller list and assign it to the master list and place the items in their respective locations?

Any help would be incredibly appreciated.

 

[jraj1106]

Hi..
The data provided are in one sheet or 3 different sheets?

Regards...

 

[Sektor]

jraj1106, "The data ... are ..."?

 

[jraj1106]

Sorry. Didn't get you.
frankshank******** type=text/javascript> vbmenu_register("postmenu_3049502", true); *********> has listed the data in his specification but wanted to know whether the whole process is on one single sheet or different sheets...

 

[RonB1111]

Assume the master has headers in row1, data starts in row2, col A is neighborhood, col B is cars, col C is trucks. Also assume the smaller list is assigned the range name of "truck". Enter this formula in C2 and copy down:

for excel versions prior to 2007:
=IF(ISNA(VLOOKUP(A3,truck,2,FALSE)),0,VLOOKUP(A3,truck,2,FALSE))

for excel 2007+:
=IFERROR(VLOOKUP(A3,truck,2,FALSE),0)

 

[frankshank]

RonB1111,

Thank you soooo much. It worked! You have no idea how much time you have saved me. I was wondering if you wouldn't mind walking me through the concept of the formula, just so that I understand and am able to conceptualize what you did?

Thanks for all of your help.

 

[frankshank]

They are on different worksheets currently but I can bring them all onto a workable sheet to do the calculations if needed.

 

[jraj1106]

Hi...
Try this code:

Sub test()
Dim neigh As Variant, i As Long, rcnt1 As Integer, rcnt2 As Integer

rcnt1 = Range("A" & Rows.Count).End(xlUp).Row
rcnt2 = Range("I" & Rows.Count).End(xlUp).Row
j = 2

For i = 2 To rcnt1
If Range("A" & i).Value = Range("I" & j).Value Then
Range("O" & i).Value = Range("A" & i).Value
Range("P" & i).Value = Range("B" & i).Value
Range("Q" & i).Value = Range("J" & j).Value
j = j + 1
End If
If Range("A" & i).Value < Range("I" & j).Value Then
Range("O" & i).Value = Range("A" & i).Value
Range("P" & i).Value = Range("B" & i).Value
Else
Range("O" & i).Value = Range("A" & i).Value
Range("P" & i).Value = Range("B" & i).Value
End If
Next
End Sub

I have placed the bigger list in Range(A2:B8).
The smaller list in Range(I2,J5)
The output in Range(O2,Q7)

All in same sheet ie. sheet3.

Please change the cell coordinates and sheet name on your side. Or else send me the sample data the way you have and I 'll test the code and send it back to you.

Regards...

 

[RonB1111]

I realize I should have said the headers are in row 2 and data starts in row 3., but I see you probably figured that out and corrected for it already. It doesn't matter whether your smaller list (that I assigned a range name of truck) is on the same or separate sheet as the master list.

Here's an explanation of the formulas:

=VLOOKUP(A3,truck,2,FALSE) placed in master sheet in cell C3:
VLOOKUP allows you to take a value in a cell, say A3 which is neighborhood 1001, and find that value in the 1st column of a rectangular group of cell (eg: your group of cells that I assigned a range name of "truck", then when it finds this in the 1st column of truck, it goes to the 2nd column to the right and returns that value to your master sheet C3 where you entered this formula. The reason for "FALSE" is to tell the formula to only return a value when there is an exact match - when there's no exact match of A3 in the 1st column of the range truck, the formula will return a #N/A.

Since you don't want to see #N/A's on your master sheet because it will cause formulas referencing these cells not to work, you tell excel to return the value 0 in this case (alternatively you could have said to return blanks which is "" or to return any comment which is entered in place of the 0 and is surrounded by "'s). In excel 2007+ you do this by wrapping the vlookup inside and =IFERROR(yourformula, result if any error such as #N/A). Earlier versions of excel don't have =IFERROR() so you wrap it inside and IF and ISNA statement as shown in my prior post.