Moving data based on value to different columns.

xoomoon

Board Regular
Joined
Oct 31, 2015
Messages
75
hello,

I have data on the left column and would like to move them to the correct column.

For instance the value for b2=red and a2= the data for red and i want it to move it to column d2.

Is there a formula for this?
Also, is there a formula for a range so that cell d2 will contain all data based on a value within a range of cells?

Thanks
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Without seeing your data, your question is a bit confusing. It might help if you can show us an example, of that your data looks like, and an image of what you want your expected output to look like.
You can post images using the tool mentioned here: XL2BB - Excel Range to BBCode
 
Upvote 0
Thanks for your reply.
Here you go.

I want the barcode numbers in the left column to auto-populate to the right cells, in this case to E3. A3= E3, etc .




Untitled.jpg
 
Upvote 0
something like this?

LocationDateBdGlassMechNo keysPartsPmSellTagTiresÜber
34.1927754, -118.34979482020.09.01 00:53:0994150700
34.1929033, -118.34992862020.09.01 00:52:4993757576
34.1929281, -118.34999462020.09.01 00:49:3794417606
34.1929333, -118.34998292020.09.01 00:49:0393453850
34.1929592, -118.34984172020.09.01 00:53:4193372591
34.1930003, -118.34999112020.09.01 00:50:5293071495
34.1931410, -118.34996232020.09.01 00:48:5290331135
34.1931450, -118.35013932020.09.01 00:48:2295032291
34.1931671, -118.35037452020.09.01 00:52:3394935831
34.1931715, -118.35030792020.09.01 00:51:1094022412
34.1931962, -118.35004002020.09.01 00:48:3791793973
34.1932640, -118.35041622020.09.01 00:51:3492059074
34.1932642,-118.35039942020.09.01 00:51:2283799273
34.1932841, -118.34997892020.09.01 00:50:4090382526
34.1932996, -118.35061222020.09.01 01:14:0493021235
34.1933472, -118.34895582020.09.01 00:54:2994936074
34.1955639, -118.36128902020.09.01 01:47:4793519053
34.1955948.. -118.36127612020.09.01 01:48:0190036844
34.1956630.. -118.36126602020.09.01 01:48:1291984056
34.1956917, -118.36216302020.09.01 01:47:2593965572
34.1958766, -118.36125332020.09.01 01:48:2790666192
34.1960219, -11S.36129S42020.09.01 01:48:3792210532
34.1960682, -118.36125322020.09.01 01:48:5694058285
34.1961343, -118.36128642020.09.01 01:49:2614242244
 
Upvote 0
Power Query M code for above solution (post#4)
Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Barcode", type text}}),
    Pivot = Table.Pivot(Type, List.Distinct(Type[Name]), "Name", "Barcode")
in
    Pivot
Anyway to move those numbers to the top so there's no spacing?
to do this you'll need vba I suppose or any new worksheet functions
 
Upvote 0
or you can do that manually
BdGlassMechNo keysPartsPmSellTagTiresÜber
94058285939655729402241294936074950322919441760693071495930212359493583183799273
922105329351905393372591941507009205907493757576
91984056903825269033113593453850
9066619291793973
90036844
14242244

- convert table to range
- remove unnecessary columns
- separate columns
- sort each column A-Z
- join columns back
 
Upvote 0
A way using formulas.

Book1
ABCDEFGHIJKLMN
1NameLocationSum of BarcodeBdGlassMechNo keysPartsPmSellTagTiresÜber
2Bd34.1955948.. -118.36127619003684414242244935190539038252694936074950322919033113592059074930212359179397383799273
3Bd34.1956630.. -118.361266091984056900368449396557293372591  9415070093071495 93453850 
4Bd34.1958766, -118.36125339066619290666192 94022412  94417606  93757576 
5Bd34.1960219, -11S.36129S49221053291984056       94935831 
6Bd34.1960682, -118.36125329405828592210532         
7Bd34.1961343, -118.36128641424224494058285         
8Glass34.1955639, -118.361289093519053          
9Glass34.1956917, -118.362163093965572          
10Mech34.1929592, -118.349841793372591          
11Mech34.1931715, -118.350307994022412          
12Mech34.1932841, -118.349978990382526          
13No keys34.1933472, -118.348955894936074          
14Parts34.1931450, -118.350139395032291          
15Pm34.1927754, -118.349794894150700          
16Pm34.1929281, -118.349994694417606          
17Pm34.1931410, -118.349962390331135          
18Sell34.1930003, -118.349991193071495          
19Sell34.1932640, -118.350416292059074          
20Tag34.1932996, -118.350612293021235          
21Tires34.1929033, -118.349928693757576          
22Tires34.1929333, -118.349982993453850          
23Tires34.1931671, -118.350374594935831          
24Tires34.1931962, -118.350040091793973          
25Über34.1932642,-118.350399483799273          
Sheet5
Cell Formulas
RangeFormula
E2:N25E2=IFERROR(AGGREGATE(15,6,$C$2:$C$25/($A$2:$A$25=E$1),ROWS($A$1:A1)),"")
 
Upvote 0
You can do the same with the new dynamic array functions available in office 365.

e.g.

E1: =TRANSPOSE(UNIQUE(Table1[Name]))
E2: =FILTER(Table1[[Barcode]:[Barcode]],Table1[[Name]:[Name]]=E1,"")

Then just drag the formula in E2 over to the right.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top