Extract data from table to another

PsychX

New Member
Joined
Jan 10, 2018
Messages
7
Hi All,
i have problem with my excel table. I have table(sheet BA11) with almost 4k row and in this table are subtable repeated every 30-36 row. Every subtable has same layout but not same number of row.
I need extract from table 2 value and this is Name and Total. Name alwas start NAME: xxx xxx and his position is A4, A39, A73 etc. and Total was in G30, G35, G99 etc. i need extract this list of name and his total to next table(sheet)

Name xxx xxx 15
Name xxy xxy 11
Name xxz xxz 17

Can me anybody help me? i Can try use index, match, offset but i have always problem with not same numbers of row between values.
Thanks.
Marcel
 
Last edited by a moderator:

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
I think this is an example where it would be really helpful to see what your data actually looks like.

You cannot upload files to this site. But there are tools you can use to post screen images. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html.
Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.
 
Upvote 0
I think this is an example where it would be really helpful to see what your data actually looks like.

You cannot upload files to this site. But there are tools you can use to post screen images. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html.
Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.

thanks for answer. i try get picture.
there is picture how to looks like.
My table was almost 4000 row and 20 sheet. I need extract NAME SURNAME and Total to new cell or to new sheet.
Problem is my SUB TABLE A1:L34 was not always same lenght, first subtable was 34 row, second was 34, three was 33, fourth was 35 etc. Range can be from 30 to 35 or 36.
In picture is show first 3 subtable, always looks like this. same text but not same lenght.

d410aL9.png
 
Last edited by a moderator:
Upvote 0
Still waiting for a good picture.

Per my Private Message to you yesterday, it looks like you still have not added MrExcel to the white list of your pop-up blocker, so you are getting the bad code at the end of all your post. I keep removing it.
Since you have not been able to correct that, I have modified the options on your editor to prevent that from happening.
 
Upvote 0
sorry my mistake, i write from computer in my work and i forget setting popup blocker here.
and back to my problem
i can share sample file and picture to onedrive and share it here.

https://1drv.ms/f/s!Aiu0A7GTjDs7nhygpwIfckQY163P

in sample is cca 10 subtable and reade data name surname and total to separate table in same sheet. but in need create formula =A4+someting and = G30+someting or someting elso what can i copy and paste to next row in position Q9 and R9 and read data from A140 and G166 etc Q10andR10 read data from A173andGxxx
 
Upvote 0
I do not have the ability to view that file from my current location (my workplace blocks those sites and internet downloads). I may be able to take a look at it tonight when I am at home.
 
Upvote 0
I can see it now. The structure is making things much more difficult than they need to be.
If it were up to me (and if it is possible), I might consider doing the following: Get the name to appear on each data line.
Then you can use SUMIF or SUMIFS to get your totals. Or even use Subtotals or Pivot Tables.

But the way you have it now, with the name only appearing at the top, and the data not being the same number of rows apart each time, it is going to be a bit difficult.
The only other option I can think of is to use VBA, and loop through each and every row to get all the names and values.
 
Upvote 0
ok. i know. that is reason what i have a problem.
can you write this VBA, because i never did it. I am terified with my table and this is only first time. I need it every month.
 
Upvote 0
It would take a little while, and I will need to do it from home where I can see your images. I may have some time this weekend to look at it.
But I need a few things clarified first:

1. What column are we summing?
2. Where exactly are we placing these subtotals? On the same page or a separate page?
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

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