Take data from one worksheet to populate another

  • Thread starter Thread starter Josh
  • Start date Start date
J

Josh

I have a data list of 48000 entries of which all cells are filled and
complete. I then have another list that has 300+ entries that has 4
cells within each with missing information. I was needing to know a
way to populate these cells with either a formula or a macro to take
the corresponding/matching cells within the first list to populate the
second.

Thanks
 
If the first list has enough information to make the line unique in the
first column then you could use vlookups in the target blank cells to find
the missing data.

ie:

say you name the source range Data and the first column of your target range
matches the entry in the first column of the source.

you could use the following vlookup in the blank cells

=vlookup(Cell in first column, Data, col(CurrentCell)-col(cell in first
column)+1,false)


Say your Data looks like this

A B C
1 1st Item1 Item2
2 2nd Item3 Item4
3 3rd Item5 Item6

Name the above range Data

Say your target range looks like this

A B C
1 1st Item2
2 2nd Item3 Item4
3 3rd Item5

Your formula in B1 would be
=vlookup($A1,Data,column(B1)-column($A1)+1,false)

This would return Item1

You could then copy this cell to the clipboard and
then choose Edit/Goto/Special/Current Region
then choose Edit/Goto/Special/Blanks
and paste the formula, all the missing data will now be filled in.

There are other methods but this one has worked for me in the past.

Hope this helps,

Sincerely, Peter Bobrowski.

Will return the missing data in the current cell.
 
Hi


Vhen you want to get info from some particular cell on same row with
matching parameter, you can use VLOOKUP function
=VLOOKUP(SearchValue,DatalistRange,ColumnNumber,FALSE)
where
SearchValue is value to search for, or reference to cell with it;
DatalistRange ist the range of cells, containing table you are
searching for SearchValue. The leftmost column of DatalistRange must
contain the value you are searching for, and it must contain the
column with return value.
ColumnNumber is number of column with return value in DatalistRange

Example: You have on Sheet2 the table
Date, Index, Name, Value
with data in rows 2:100
and you want to return Value for Index=123
=VLOOKUP(123,Sheet2!B2:D100,3,FALSE)

NB! The first occurence of 123 is detected!


Arvi Laanemets
 
Back
Top