Rearrange data in a new TAB

ManuF

New Member
Joined
Jun 23, 2022
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
Hi all,



I need help with a VBA code. I would like to rearrange data in my spreadsheet . Right now it is easy because I have a unique sample name in each row. After a software update the result file changed.

1. I need to copy the cities (green)from the city column to the title in the result tab (because it may change in number and order from result file to result file).

2. With the sample name on the front in the result tab.

3. Then transpose the values underneath.

I’m sorry just can wrap my head around it.

Thanks in advance for your help.
All.jpg
result.jpg
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Column A and row 1 could be done with "Get unique distinct list from single column", try to google
The value in the body could be done by SUMIFS
Try it.
If you need fast help with solution, attach a mini sheet via XL2BB tool
 
Upvote 0
Hi bebo021999!

thank you very much for your quick answer. with the "Get unique distinct..." I can make the "sample" list and the "analyte" list on the top.
But I don't want to sum up the values just need to rearrange the for every "sample" . Basicly copy the range and transpose them next to the corresponding sample name.
 
Upvote 0
Why not sum up?
If alfa-berlin is unique, SUMIFS does not effect to the result
otherwhile, you have to use INDEX( COLUMN C, MATCH(1, (COLUMN 1=alfa) * (COLUMN 2= berlin)) which is array formula, for your version 2016, require Ctrl-Shift_enter
 
Upvote 0
You can achieve this through Pivot Table

Book4
ABCDEFGH
1SampleCityValue
2AX2.41
3AY4.55
4AZ3.25Sum of ValueColumn Labels
5BY1.11Row LabelsXYZ
6BZ6.54A2.414.553.25
7CX6.27B1.116.54
8CY4.94C6.274.943.67
9CZ3.67
Sheet1
 
Upvote 0
Thank you very much bebo ... got it!
Thank you Sanjeev I will look into it!
Can I populate row 5 and Coumn E with a unique distinct function?
The arrys are on the different TAB. That should still work right?
 
Upvote 0

Forum statistics

Threads
1,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

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