Excel help

Remove this Banner Ad

RU_

.-.-.-.-.-.-.-.
Aug 6, 2010
22,596
15,113
Australia
AFL Club
Brisbane Lions
Hi all, hoping you might be able to help me with something that will streamline some of my employment excel work.

I’ve got two excel spreadsheets.
Spreadsheet A is from last week and outdated.
Spreadsheet B is from this week and the current one with my updated project list.

Spreadsheet A has my notes in column M of excel but doesn’t have my latest projects list.

Can I merge the job numbers in Column A and the notes of Column M in Spreadsheet A (outdated spreadsheet with notes) into spreadsheet B so that the vacant notes rows will be my new projects?
 

Log in to remove this ad.

kaiserchief13

Hall of Famer
Suspended
Jul 12, 2011
30,360
21,594
Melbourne
AFL Club
West Coast
Other Teams
Eagles, Lakers, Bayern, Trojans
Xlookup? index/match? vba macro using row.count to get row number variable?

Maybe provide screenshots as it's not 100% clear what the problem is
 

randyzany

Premiership Player
Sep 21, 2007
3,859
1,622
Melbourne
AFL Club
Geelong
Seems the OP query has been answered so I hope the OP doesn't mind using this thread instead of making a new one.

I want to create a table or spreadsheet (whichever is easier) from 2 sets of data which are small tables themselves.

1 set of data contains the column reference of a particular value and the other set of data contains the row reference of that value.

The Column Headers are the generic C1, C2, C3, etc.. and the Row Headers R1, R2, R3, etc...



I have these sets of data in the form of 2 mini tables:

VALUE: 1, 2, etc..
COLUMN: 8, 12, etc..


VALUE: 1, 2, etc..
ROW: 5, 6, etc..

From these mini tables I want to make a larger table that places Value 1 in Column 8 and Row 5, Value 2 in C12,R6, etc..

Been googling for a while with no luck in finding out to combine data from 2 tables to form a new one.
 
Last edited:

Caesar

Ex-Huckleberry
Mar 3, 2005
28,186
14,026
Tombstone, AZ
AFL Club
Western Bulldogs
Seems the OP query has been answered so I hope the OP doesn't mind using this thread instead of making a new one.

I want to create a table or spreadsheet (whichever is easier) from 2 sets of data which are small tables themselves.

1 set of data contains the column reference of a particular value and the other set of data contains the row reference of that value.

The Column Headers are the generic C1, C2, C3, etc.. and the Row Headers R1, R2, R3, etc...



I have these sets of data in the form of 2 mini tables:

VALUE: 1, 2, etc..
COLUMN: 8, 12, etc..


VALUE: 1, 2, etc..
ROW: 5, 6, etc..

From these mini tables I want to make a larger table that places Value 1 in Column 8 and Row 5, Value 2 in C12,R6, etc..

Been googling for a while with no luck in finding out to combine data from 2 tables to form a new one.
the search phrases I would use for this problem are 'join tables in Excel' and 'convert table to matrix in Excel'

there are a few different ways to do it, but (assuming you don't have duplicate values) I would use Power Query to join the two tables on the Value column, then use the Pivot Column function to create your columns (under advanced options, select Don't Aggregate)

example file attached
 

Attachments

  • createMatrix.xlsx
    21.5 KB · Views: 13

randyzany

Premiership Player
Sep 21, 2007
3,859
1,622
Melbourne
AFL Club
Geelong
When I first started searching for a solution for this task on Excel I just assumed that it would be fairly straightforward and need only the Excel basics (like stuff you learnt for school assignments) for it to complete it. Have to brush up on those matrices and arrays, thanks for the hints.

Oh and I appreciate the template you've uploaded, thanks again.
 

Remove this Banner Ad

Remove this Banner Ad